I use the following code to insert data from a form into a google sheet. It works properly and adds data correctly. The problem is when I want to add data to a column that is not on my worksheet. I know that I have to check if such a column exists in the worksheet. If it does not exist, add it. And only then add a row with new data. Unfortunately I'm a freshman in Apps Script and I don't know how to do it

// In case you want to change the Sheet name
var sheetName = 'xyz'
var scriptProp = PropertiesService.getScriptProperties()

// Lowercasing all input keys in the POST data by default (to avoid Message vs message confusion)
var shouldLowerCaseHeaders = true

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

function filterRow (parameters, mandatoryFields) {
    return mandatoryFields.every(field => parameters[field.toString().toLowerCase()] && parameters[field.toString().toLowerCase()].length > 0)
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)
  // Uncomment and add fields which must be mandatory when submitting a form
  //const mandatoryFields = ['questions']
  const mandatoryFields = []

  try {
    // Get the current open Google Sheet
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    // IMPORTANT: Create headers in your google sheet first
    //            If you dont create headers this won't match the data
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow()   1

    var parameters = e.parameter;

    // Lower casing header keys - True by default
    if (shouldLowerCaseHeaders){
      Object.entries(e.parameter).map(([key, value]) => parameters[key.toString().toLocaleLowerCase()] = value)
    }

    const shouldInsertToSheet = filterRow(parameters, mandatoryFields)

    if (shouldInsertToSheet){
      var newRow = headers.map(function(header) {
        return header.toString().toLowerCase() === 'timestamp' ? new Date() : parameters[header.toString().toLowerCase()]
      })
      sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
    }

    return HtmlService.createHtmlOutput("post request received");
  }

  catch (e) {
      return HtmlService.createHtmlOutput("post request received");
  }

  finally {
    lock.releaseLock()
  }
}

How to check if column header exists and if not add header column?

CodePudding user response：

If you want to add a header that is not included in the initial values, just compare those you have with a new list, if any of the ones that belong to the new list are not included, add them at the end of the headers.

Assuming that the headers of your table are these:

Code.gs

const sS = SpreadsheetApp.getActiveSheet()

function appendHeaderIfNotExist(headersResponse) {
  const actualHeaders = sS.getRange(1, 1, 1, sS.getLastColumn()).getValues().flat()
  headersResponse.forEach(h => {
    if (!actualHeaders.includes(h)) sS.getRange(1, sS.getLastColumn()   1).setValue(h)
  })
}

function testImplementation() {
  appendHeader(['Header1', 'Header5'])
}

After running the function appendHeaderIfNotExist:

You will have to adapt it to work inside your script, probably by parsing the content of the response and calling the function with the array of keys of the response.