Cannot correctly get form data in google script-CodePudding

I try to submit form data to google sheet via ajax but google script if else statement not work.

Below is the google script

// original from: http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
// original gist: https://gist.github.com/willpatera/ee41ae374d3c9839c2d6 

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

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

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

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){

      switch (headers[i]) {
        case "Timestamp":
          row.push(new Date());
          break;
        case "LogTime":
          row.push(new Date());
          break;
        default:
          row.push(e.parameter[headers[i]]);
          break;
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.appendRow(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());
}

I submit the form data using ajax

HTML

<form id="checkin-form">
<input type="hidden" name="Event"  value="<?php echo $events[1]; ?>"/>
<input type="hidden" name="LogTime" value="" />
...
</form>

Javascript

$('#checkin-form').on('submit', function(e) {
    e.preventDefault();
    var jqxhr = $.ajax({
        crossDomain: true,
        url: url,
        method: "GET",
        dataType: "json",
        data: $("#checkin-form").serialize()
    });
    return false;
});

From google script, the column LogTime cannot get the Date() but keep empty. What is the possible issue?

Here is the google sheet column

Best regards,

Kelvin

CodePudding user response：

From your script and question, I guessed that you might send the data of the HTML form to Web Apps (doGet) using ajax. If my understanding is correct, how about the following modification?

Javascript side:

$('#checkin-form').on('submit', function(e) {
  const url = "https://script.google.com/macros/s/###/exec"; // Please set your Web Apps URL.
  e.preventDefault();
  $.ajax({
    url: url   "?"   $("#checkin-form").serialize(),
    method: "GET",
  })
    .done((res) => console.log(res))
    .fail((err) => console.log(err));
    return false;
});

Google Apps Script side:

function doGet(e) {
  var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
  var sheet = doc.getSheetByName(SHEET_NAME);
  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 if (headers[i] == "LogTime") {
      var now = new Date();
      row.push(now);
    } else { // else use header name to get data
      row.push(e.parameter[headers[i]]);
    }
  }

  // Add the row to the sheet.
  sheet.appendRow(row);
  return ContentService.createTextOutput("done");
}

Note:

In this case, it is required to prepare the header row to your Spreadsheet. Please be careful this.
Unfortunately, from your provided script, I couldn't imagine your whole script. So I proposed the above modified script by my guess. Please modify your actual script using my proposed modified script.
In your script, I guessed that Web Apps is used. In this case, when you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

References:

CodePudding user response：

Don't know why the google apps script does not work, finally, I found the article from https://medium.com/@dmccoy/how-to-submit-an-html-form-to-google-sheets-without-google-forms-b833952cc175, when I redo running the Setup function, everything works properly. The Setup function has already been run before and the script worked.