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 belowJ3
are blank too.Regarding the script you should have to change the following code line
Just replace
10
by the column number to be used for the response urls.