0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.