Skip to content

Instantly share code, notes, and snippets.

@HoukasaurusRex
Created August 19, 2019 10:10
  • Star 1 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save HoukasaurusRex/54b8112a4169c84ba20535a46c92be71 to your computer and use it in GitHub Desktop.
POST data to Google Sheets using Google Scripts
// Original: https://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
// Usage
// 1. Enter sheet name where data is to be written below
var sheetName = 'Users'
// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var scriptProp = PropertiesService.getScriptProperties()
function setup () {
const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function parseBody (event) {
try {
return JSON.parse(event.postData.contents)
} catch (error) {
throw new Error('Bad body data: ' + error.message)
}
}
function doPost (event) {
const lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
const sheet = doc.getSheetByName(sheetName)
if (!sheet) {
throw new Error('Could not find Google Sheet named: ' + sheetName)
}
const body = parseBody(event)
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
const nextRow = sheet.getLastRow() + 1
const newRow = headers.map(function(header) {
return header === 'timestamp' ? new Date() : body[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow, 'data': body }))
.setMimeType(ContentService.MimeType.JSON)
}
catch (error) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': error }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}
const gscriptUrl = `https://script.google.com/macros/s/${scriptId}/exec`
const body = {
name: 'Pterobyte',
hobbies: 'data, actually, cant, be, an, array, dinosaurs',
meta: {
objects: 'turn',
out: 'pretty',
weird: 'too'
}
}
const fetchOptions = {
method: 'POST',
body: JSON.stringify(body)
}
fetch(gscriptUrl, fetchOptions)
.then(res => res.json())
.then(console.log)
.catch(console.error)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment