Google-sheets – Add fields and update array and scripts

google sheetsgoogle-apps-scriptgoogle-formsgoogle-sheets-arrayformula

I want to add some questions to my Google form but my response sheet has a script and an array, which do not like that.

Here is what it should look like:

The URL is the link for the form.

Here is the edit script:

function assignEditUrls() {
  var form = FormApp.openById('1g1RYJ25dBA_CB2pzL6CYQDq9WlcwGEeL_C8q0dYgxnY');

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');

  var data = sheet.getDataRange().getValues();
  var urlCol = 10; 
  var responses = form.getResponses();
  var timestamps = [], urls = [], resultUrls = [];

  for (var i = 0; i < responses.length; i++) {
    timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
    urls.push(responses[i].getEditResponseUrl());
  }
  for (var j = 1; j < data.length; j++) {

    resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
  }
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  
}

And here is the Array:

=ArrayFormula(if(len(A2:A), "19AM" & ROW( A2:A )-2 + 100000 ,""))

I want to add 2 questions to my form.

Here is what I get:

When I copy the Array formula into J2, this is what I get:

#REF! Error Array results could not be expanded because it would overwrite the data in J3

Best Answer

As the error says, the results could not be expanded because J3 is not blank (empty). Also check that the cells below J3 are blank too.

Regarding the script you should have to change the following code line

var urlCol = 10; 

Just replace 10 by the column number to be used for the response urls.