I have a script that is designed to create about 120 forms and link them to a single spreadsheet where I will analyze the data. I don't have any issues with the script until my spreadsheet has about a dozen forms linked to it. Then I get an error saying the destination ID is invalid, after logging the id, and entering it manually into a url I see no issues with the ID....
var ssSummaryId = '******redacted*******';
var form = FormApp.create('RM#' + rmNumber).setDestination(FormApp.DestinationType.SPREADSHEET, ssSummaryId);
form.setDescription(valuesSummary[ii][2])
.setConfirmationMessage('Thanks for the update on room ' + rmNumber)
.setShowLinkToRespondAgain(false);
// form.setDestination(FormApp.DestinationType.SPREADSHEET, ssSummaryId);
var formId = form.getId();
var formUrl = form.getPublishedUrl();
EDIT I'm adding my complete script and some additional info, just in case someone wants to check my code out and point out all it rookie mistakes.
I'm using Google scripts to build a spreadsheet and then create 120 slightly altered Google forms that are linked to a single spreadsheet, all the responses are by design on separate sheets named "form responses n". I consistently hit a wall once I exceed 10 forms linked to one sheet. Note; in initial testing I remember having a spreadsheet with 46 forms (and therefore sheets) linked to it. As you can see in the code below, I have the app restart from where it's left off after every 5 forms are created, so I'm not getting any 'extended runtime errors". Just the error below, typically after the script runs twice from Google Scripts IDE.
I'm just finally getting the hand of basic javascript after years of using and modifying js in web developing. So I apologize in advanced for the poor code.
Failed to set response destination. Verify the destination ID and try again. (line 54, file "Code")
function spreadsheet_builder() {
var ssSummaryId = '<<REDACTED>>';
var ssFormDataId = '<<REDACTED>>';
var batchSize = 5;
var buildStatus = false;
var ssSummary = SpreadsheetApp.openById(ssSummaryId);
SpreadsheetApp.setActiveSpreadsheet(ssSummary);
if (ssSummary.getSheetByName('Summary') == null) {
var sheetSummary = ssSummary.getSheetByName('Sheet1');
} else {
var sheetSummary = ssSummary.getSheetByName('Summary');
}
var rangeSummary = sheetSummary.getDataRange();
var valuesSummary = rangeSummary.getValues();
buildStatus = get_last_position(valuesSummary, buildStatus); //either returns last position in array or 'true' if task is complete
if (buildStatus != true || buildStatus > 0) {
var formCreation = [];
var formData = get_form_data(ssFormDataId); // Get form questions from form Data ss, might be better to keep everything on the same sheet
batchSize = buildStatus + batchSize;
for ( var ii = buildStatus; ii < batchSize; ii++ ) {
if (valuesSummary[ii][1] != '') {
var formCreationReturn = form_builder(formData, valuesSummary, ii, ssSummaryId);
formCreation.push(formCreationReturn);
}
}
var aSum = [ssSummary, sheetSummary, rangeSummary];
final_storing_operation(formCreation, aSum, buildStatus);
}
if (sheetSummary.getName() != 'Summary') {
SpreadsheetApp.setActiveSpreadsheet(ssSummary);
sheetSummary.activate().setName('Summary');
sheetSummary.setFrozenColumns(3);
sheetSummary.setFrozenRows(1);
sheetSummary.hideColumns(1);
//var sSumIndex = sheetSummary.getIndex();
}
SpreadsheetApp.setActiveSpreadsheet(ssSummary);
sheetSummary.activate();
ssSummary.moveActiveSheet(1);
}
function form_builder(formData, valuesSummary, ii, ssSummaryId) {
var lastFormCreated = ii;
var rmNumber = valuesSummary[ii][1];
var form = FormApp.create('RM#' + rmNumber).setDestination(FormApp.DestinationType.SPREADSHEET, ssSummaryId);
form.setDescription(valuesSummary[ii][2])
.setConfirmationMessage('Thanks for the update on room ' + rmNumber)
.setShowLinkToRespondAgain(false);
// form.setDestination(FormApp.DestinationType.SPREADSHEET, ssSummaryId);
var formId = form.getId();
var formUrl = form.getPublishedUrl();
var sectionHeader = 'SECTION_HEADER'; //preformatted form question types.
var list = 'LIST';
var paragraphText = 'PARAGRAPH_TEXT';
for (var j = 1; j < formData.length; j++) { //top row is header
switch (formData[j][0]) {
case sectionHeader:
form.addSectionHeaderItem().setTitle(formData[j][1]);
break;
case list:
var item = form.addListItem();
item.setTitle(formData[j][1]).setHelpText(formData[j][2]);
item.setChoices([
item.createChoice(formData[j][3]),
item.createChoice(formData[j][4]),
item.createChoice(formData[j][5])
]);
break;
case paragraphText:
form.addParagraphTextItem().setTitle(formData[j][1]);
break;
default:
form.addSectionHeaderItem().setTitle('OOPS u\'don MESSED up');
break;
}
}
return [formId, formUrl, lastFormCreated, rmNumber];
}
function final_storing_operation(formCreation, aSum, buildStatus) {
SpreadsheetApp.setActiveSpreadsheet(aSum[0]);
aSum[1].activate();
var startRow = formCreation[0][2] + 1;
var newRange = aSum[1].getRange(startRow, 1, formCreation.length, 2); //row, clmn, rows, columns
var newValues = [];
for ( var ij = 0; ij < formCreation.length; ij++) {
var values = [formCreation[ij][0], "\=HYPERLINK(\"" + formCreation[ij][1] + "\", \"RM#" + formCreation[ij][3] + "\")"];
newValues.push(values);
}
newRange.setValues(newValues);
}
function get_last_position (valuesSummary, buildStatus) {
var rowPos = 1; // start at 1 to ignore headers
while (valuesSummary[rowPos][1] != '') {
if (valuesSummary[rowPos][0] == '') {
return rowPos;
}
rowPos++;
}
if(valuesSummary[rowPos][0] != '' && valuesSummary[rowPos][1] != '') {
buildStatus = true;
return buildStatus;
}
}
function get_form_data (ssFormDataId) {
var ssFormData = SpreadsheetApp.openById(ssFormDataId);
SpreadsheetApp.setActiveSpreadsheet(ssFormData);
var sheetFormData = ssFormData.getSheets()[0];
var rangeFormData = sheetFormData.getDataRange();
var valuesFormData = rangeFormData.getValues();
return valuesFormData;
}
