Skip to content

Why is the webflow form data not showing in google sheets?

I’ve been using these codes to get data parsed from a webflow form into a google sheets, but this time, this is not working and I can’t find out why, since there’s no apparent error.

This is script embedded in the webflow site build:

<script>
const scriptURL = 'https://script.google.com/macros/s/AKfycbz5X-0FqrOgghfGyvhxbcl9D9CKZU0YWmGjxM6PzuTExBiB-ABoioImHEqx-NrrB73tXA/exec'
const form = document.forms['wf-form-Contato-Form']
  form.addEventListener('submit', e => {
    e.preventDefault();
    fetch(
      scriptURL, 
      { method: 'POST', body: new FormData(form), mode: 'no-cors'}
    )
      .then(response => console.log('Success!', response))
      .catch(error => console.error('Error!', error.message))
form.reset();  
})
</script>

This is the piece sitting in the Google Apps Script environment:

var sheetName = 'Leads'
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 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 === 'Data' ? 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()
  }
}

When the form is submitted, the network console session only shows the fields filled in, ignoring the empty ones. Not sure of headers function above requires all fields to come from the form, so that they can be mapped…

enter image description here

And the script gets executed at the apps script end with no apparent error, but nothing shows up in the sheets.

Appreciate any light towards to the reason.

Answer

Add some console.log statements to you Apps Script in order to help you to debug it.

console.log(JSON.stringify(e));

The above statement will log the web app event object.

console.log(`${sheetName} `${(!sheet ? '' : 'does't'} exists`);

The above statement could be useful to be sure that the required sheet exists.