Push selected values to specific columns

user_4735604 0
Viewed 107

My html form has a few fields, however, I only want id, Confirmation, Questions, and Notes to be posted to their respective columns on my sheet:

enter image description here

How can I achieve it? I was only able to post "all" form fields to their columns based on same header name as my form name="" value.

I only want id, Confirmation, Questions and Notes to map to their right columns.

My Code.gs:

var scriptProp = PropertiesService.getScriptProperties()

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

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)
  
  try {
    
    var sheetName = e.parameter.svdate.split("/").splice(0, 2).map(function(e){return e.replace(/^0/, "")}).join("/");
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
    var sheet = doc.getSheetByName(sheetName);
    if (!sheet) throw new Error("Sheet Name was not found.");
    
    var headers = sheet.getRange(1, 1, 1, 10).getValues()[0]
    var nextRow = sheet.getLastRow() + 1
    
    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : e.parameter[header]
    })
    
    var find = sheet.getRange(1, 1, sheet.getLastRow()).createTextFinder(newRow[0]).matchEntireCell(true).findNext(); 
    sheet.getRange(find ? find.getRow() : nextRow, 1, 1, newRow.length).setValues([newRow]);
    
    return ContentService
    .createTextOutput(e.parameter.callback + "(" + JSON.stringify({ 'result': 'success', 'row': nextRow })+ ")")
    .setMimeType(ContentService.MimeType.JAVASCRIPT);
  }
  
  catch (e) {
    return ContentService
    .createTextOutput(e.parameter.callback + "(" + JSON.stringify({ 'result': 'error', 'error': e })+ ")")
    .setMimeType(ContentService.MimeType.JAVASCRIPT)
  }
  
  finally {
    lock.releaseLock()
  }
}
google-apps-script google-sheets
1 Answers

I believe your goal as follows.

  • When the HTML form is submitted, you want to put only the values of id, Confirmation, Questions, and Notes to the Spreadsheet.

In this case, how about the following modification?

From:

var newRow = headers.map(function(header) {
  return header === 'timestamp' ? new Date() : e.parameter[header]
})

To:

var keys = ["id", "confirmation", "questions", "notes"];
var newRow = headers.map(function (header) { return header === 'timestamp' ? new Date() : keys.includes(header.toLowerCase()) ? e.parameter[header] : "" });
  • In this modification, using keys, the values of specific keys are retrieved.

Reference:

Added:

From your following replying,

Hi Tanaike. I have one issue from your suggested code. It replaces the value of the other columns with null like the Payment Method, LA Customer, Cleaners, etc.

In this case, please modify your script in your question as follows.

From:

var newRow = headers.map(function(header) {
  return header === 'timestamp' ? new Date() : e.parameter[header]
})

var find = sheet.getRange(1, 1, sheet.getLastRow()).createTextFinder(newRow[0]).matchEntireCell(true).findNext(); 
sheet.getRange(find ? find.getRow() : nextRow, 1, 1, newRow.length).setValues([newRow]);

To:

var keys = ["id", "confirmation", "questions", "notes"];
var newRow = headers.map(function (header) { return header === 'timestamp' ? new Date() : ~keys.indexOf(header.toLowerCase()) ? e.parameter[header] : "" });

var find = sheet.getRange(1, 1, sheet.getLastRow()).createTextFinder(newRow[0]).matchEntireCell(true).findNext();
if (find) {
  var range = sheet.getRange(find.getRow(), 1, 1, newRow.length);
  var rowValue = range.getValues()[0].map(function(c, i) {return newRow[i] || c});
  range.setValues([rowValue]);
} else {
  sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);
}
user_71086530
Related