I am trying to create a form that will send the results to the google sheets.

After sending it from the website, i get a "Response 200 success" however the sheet is not updated. However from Postman there are no problems.

React:

  const handleSubmit: React.FormEventHandler<HTMLFormElement> = (event) => {
    const formData = new FormData(event.currentTarget);
    event.preventDefault();
    var formObject = Object.fromEntries(formData.entries())
    console.log(formObject)

    const axios = require('axios')

    const data = {
      "organization": ['JD'],
      "role": ['DJ'],
      "twitter": ['aba'],
      "email": ['[email protected]'],
    }
    console.log(JSON.stringify(data))
    axios({
      method: 'post',
      url: googleForm,
      data: data,
      headers: {
        'Content-Type': 'text/plain;charset=utf-8',
      },
    }).then(function (response) {
      console.log("was this the response?", response);
    }).catch(function (error) {
      console.log(error);
    });
  };

App Script:

function doPost (e){
  if(!e) return ContentService.createTextOutput("No e");
  if(!e.parameters) return ContentService.createTextOutput("No params");
  if(!e.parameters.email) return ContentService.createTextOutput("No email");
  if(!e.parameters.twitter) return ContentService.createTextOutput("No twitter");
  if(!e.parameters.organization) return ContentService.createTextOutput("No organization");
  if(!e.parameters.role) return ContentService.createTextOutput("No role");

  return addToDoc(e.parameters);
}

function addToDoc(parameters) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getDocumentLock();
  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);
   
    var nextRow = sheet.getLastRow() 1; // get next row
    var row = [];
    // loop through the header columns
    row.push(parameters.organization);
    row.push(parameters.role);
    row.push(parameters.twitter);
    row.push(parameters.email);
    // 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(error){
    return ContentService.createTextOutput(JSON.stringify({"result":"error", "error": error})).setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

Response of request From The website: Website request

And response from Postman: {"result":"success","row":6}

Any ideas how to fix?

CodePudding user response：

In your situation, how about the following modification? In this modification, doPost is modified.

Modified script:

function doPost(e) {
  if (!e) return ContentService.createTextOutput("No e");
  var obj = JSON.parse(e.postData.contents);
  if (!obj.email) return ContentService.createTextOutput("No email");
  if (!obj.twitter) return ContentService.createTextOutput("No twitter");
  if (!obj.organization) return ContentService.createTextOutput("No organization");
  if (!obj.role) return ContentService.createTextOutput("No role");
  return addToDoc(obj);
}

• When your axios is run, the value of data is put to e.postData.contents while e.parameters is empty. I thought that this might be the reason of your issue.

Note:

• In your script, it seems 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".