Unable to Make Cross Origin Resource Sharing (CORS) request to Google Web App Using JS Fetch

Question

I made a google web app that receives post requests and posts the data to a google sheet. The code for the web app is below:

var sheetName = 'Sheet'

var scriptProp = PropertiesService.getScriptProperties()

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

function doPost (e) {
  
  var lock = LockService.getScriptLock()

  lock.waitLock(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]
    // Gets the last row and then adds one
    var nextRow = sheet.getLastRow() + 1

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

    
    return ContentService
    .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow, 'rows': newRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    
    lock.releaseLock()
  }
}

The link to see the sheet is: https://docs.google.com/spreadsheets/d/1w8M_Jlo4YKhkbTjlXbWybiTONjWylXSFFThNPo5e9CM/edit#gid=0

I want to submit data to the sheet using a button on my static web page so I made wrote some basic JS that use fetch to submit a post request. I kept getting blocked by the cors preflight requirement since my page is static (hosted by Github Pages) and does not have a server to respond to the cors preflight. So I added heroku CORS anywhere and I still cannot get anything to post to the sheet. I have been testing by going to girling.info, my static page(still in progress) opening the debugger console and running the code. The JS fetch code:

fetch('https://cors-anywhere.herokuapp.com/https://script.google.com/macros/s/AKfycbyjt4Yg22ERgK3FS11UIPmGE1_sBLEt-kh0vRF37rAI3ujIu5DC/exec',
       {
       method: 'POST',
       mode: 'cors',
       headers: {'Origin': 'true', 'X-Requested-With': 'true', 'User-Agent': 'python-requests/2.23.0', 'Accept-Encoding': 'gzip, deflate', 'Accept': '*/*', 'Connection': 'keep-alive'}, 
       body: {'Email': '[email protected]', 'Timestamp': 24},
       }).then(response => console.log(response));

I get back a response with code=200 but no data actually posts to the sheet.

Things I have verified:

The web app works by using the command line to submit a post request with python requests
The text in my body exactly matches the columns in the sheet

I could use some help

The web app is published so that anyone can access it. Here is a screen grab of the the settings: screen grab

I added 'cache': 'no-cache', 'redirect': 'follow' to my headers and still got the same thing. Here is a screen grab of the console. console log

By looking at the execution history for the google web api, I found that when I submit a post request using JS CORS, the web app executes but puts null in the data spots.

What is being executed; the try or the catch? They'll both give 200. — I hope this is helpful to you
The try should be executed. When I get back the cors response the body is empty so I can't tell for sure. If the try is executed I should get information about the row entered. If the catch is then I should get error in the response body, but I don't get anything. — Rowlando13
Not sure. You need to add console.logs and debug each step. Also, you don't need herokuapp. Send the request directly. — TheMaster

Rowlando13 Rowlando13 · Accepted Answer · 2021-01-01T18:54:36

The problem was with caused by not having the CORS body be formatted as a JS FormData object or something that can be accessed by

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

When I was submitted a dictionary directly in the CORS request body, I don't think the e.parameter[header] was finding anything, so it was returning null. This explains why the google app was running but inputting null. My confirmation test was making an html page on my computer with this form

<form name="submit-to-google-sheet" method="post" target=_self>
  <label for="Timestamp">Timestamp:</label> <br>
  <input name="Timestamp" type="text"  value="Automatically updated on submit" size="30" > <br>
  <label for="0001"> How many times in the last 5 years have you seen this question in an interview:</label> <br>
  <input name="0001" type="text"  value=0 size="30" required> <br>

and this script

<script>
  const scriptURL = 'https://script.google.com/macros/s/AKfycbyjt4Yg22ERgK3FS11UIPmGE1_sBLEt-kh0vRF37rAI3ujIu5DC/exec'
  // html form
  let form = document.forms['submit-to-google-sheet']

  form.addEventListener('submit', e => {
    e.preventDefault()
    // make formdata object
    let form1 = new FormData(form)
    // setting timestamp
    form1.set('Timestamp', new Date())
    fetch(scriptURL, { method: 'POST', body: form1})
      .then(response => console.log('Success!'))
      .catch(error => console.error('Error!'))
    reset_form = function (form_name, input_name) {
      document.getElementsByName(input_name)[0].value = 'Thank your for your submission!!'
    }
    reset_form('submit-to-google-sheets', 'Timestamp')
    reset_form('submit-to-google-sheets', '0001')
  })
</script>

Thanks for all the help!

Unable to Make Cross Origin Resource Sharing (CORS) request to Google Web App Using JS Fetch

2 Answers