javascriptreactjsgoogle-sheetsgoogle-apps-scriptweb-applications

Error in Google App Script when submitting form data to Google Sheets


The goal is to send with the form data in Google Sheets to be recorded in the appropriate fields. I used this article, but my script gives an error:

{
"result": "error",
  "Error in: ": {
  "message": "Invalid argument: id",
  "name": "Exception",
  "fileName": "Code",
  "lineNumber": 20,
  ""smack": "\that Code:20 (handleResponse)\n\tat Code:2 (doGet)\n"
  }
}

Please help me to find out what error I made in Google Apps Script, or tell me how you can diagnose the error yourself.

Google Apps Script:

function doGet(e){
  return handleResponse(e);
}

//  Enter sheet name where data is to be written below
        var SHEET_NAME = "Table";

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.

  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);

    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

Solution

  • You are getting this error because you forgot to perform the addition of the Google Apps Script.

    To Do this - Go to the “Run” menu and select “setup.”