Google Apps Script: Trouble Passing URL Params from doGet() to doPost()

Question

I'm having trouble getting a variable passed from doGet() into doPost(). When the variable sheetName is defined globally without any logic to define it, it works fine, but when I use doGet() to define it using a URL parameter, it won't load the form submission data into my Google Sheet. Basically I'm trying to define the sheet name so I can reuse the code for separate tabs inside the same sheet. Changing sheetName to 'Sheet2' works fine and passes the data into that sheet (assuming the headers are setup properly). I feel like it might be a load order where doGet() is getting called after doPost() or something? Any help would be greatly appreciated.

The base of the code I'm using was taken from here: https://forum.webflow.com/t/how-to-submit-html-form-directly-to-google-sheets/71432

my scriptURL is something like this: https://script.google.com/macros/s/KEY/exec?formUsed=Sheet1

My modified Code.gs code is below:

var sheetName;

function doGet(request){
  sheetName = request.parameter.formUsed;
  return ContentService.createTextOutput(sheetName);
}

//var sheetName = 'Sheet1';
var scriptProp = PropertiesService.getScriptProperties();

function setup() {
  var doc = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', doc.getId())
}

function doPost(e) {
  var lock = LockService.getScriptLock()
  lock.waitLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1
    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
    return ContentService.createTextOutput(JSON.stringify({
      'result': 'success',
      'row': nextRow
    })).setMimeType(ContentService.MimeType.JSON)
  } catch (e) {
    return ContentService.createTextOutput(JSON.stringify({
      'result': 'error',
      'error': e
    })).setMimeType(ContentService.MimeType.JSON)
  } finally {
    lock.releaseLock()
  }
}

Answer 1

When you send a GET request to your script, it will set the value of sheetName, and then return that to the user. After returning, the script finishes execution and sheetName is no longer defined.

Then, when you receive the POST request another, separate instance of the script is executed, so sheetName is undefined.

The variables only hold their values for the single execution instance. To retain values across executions, you should save to Properties.

var scriptProp = PropertiesService.getScriptProperties();

function doGet(request){
  scriptProp.setProperty('sheetName', request.parameter.formUsed);
  return ContentService.createTextOutput(sheetName);
}

function setup() {
  var doc = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', doc.getId())
}

function doPost(e) {
  var lock = LockService.getScriptLock()
  lock.waitLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(scriptProp.getProperty('sheetName'))
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1
    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
    return ContentService.createTextOutput(JSON.stringify({
      'result': 'success',
      'row': nextRow
    })).setMimeType(ContentService.MimeType.JSON)
  } catch (e) {
    return ContentService.createTextOutput(JSON.stringify({
      'result': 'error',
      'error': e
    })).setMimeType(ContentService.MimeType.JSON)
  } finally {
    lock.releaseLock()
  }
}