Skip to content

Instantly share code, notes, and snippets.

@triblondon
Last active August 31, 2022 11:43
Show Gist options
  • Save triblondon/854315a40d280a22fd89804e332fd7b3 to your computer and use it in GitHub Desktop.
Save triblondon/854315a40d280a22fd89804e332fd7b3 to your computer and use it in GitHub Desktop.
Google apps script for form data to gsheet
// Submit form data directly to Google sheets
//
// (Based on http://railsrescue.com/blog/2015-05-28-step-by-step-setup-to-send-form-data-to-google-sheets/)
//
// ## Setup
//
// 1. Create a Google spreadsheet and name it
// 2. Choose Tools > Script editor
// 3. Replace the sample code with the contents of this file
// 4. Name the script project something like "Submit form data to Google sheet" (click on 'Untitled project' to change name)
// 5. Choose Run > setup
// 6. Approve the authorisation prompt
// 7. Choose Resources > Current project's triggers, and create a trigger for `doPost` + `From spreadsheet` + `On form submit`
// 8. Choose Publish > Deploy as web app. Project version 'new', access for 'anyone, even anonymous', execute as 'me'
// 9. Copy the provided endpoint URL
// 10. Send POST data to the endpoint as `application/x-www-form-urlencoded` (query string in request body)
//
// ## POST format
//
// Send POST data with fields that match the column headers of your spreadsheet, lowercased. Some fields also have some special meanings:
//
// - `sheet_name`: Name of sheet within spreadsheet doc. Defaults to 'Sheet1'
//
// Some column names in your spreadsheet are also special:
//
// - `id`: Will be populated normally if you send an `id` field from your form POST, but
// if not, any id column will be populated by an auto-generated UUID which will then
// be returned in the JSON response
// - `timestamp`: Will be populated with the time when the submission was received.
// Cannot be overridden by sending a timestamp field in the form POST.
//
// ## Troubleshooting
//
// - If data is not appearing in the sheet, check that you are sending it in url-encoded format, not multipart/form-data
// - If you don't see the 'Anyone, even anonymous' option, your Google Apps domain admin has disabled public sharing
//
////////////////////////////////////////////////////////////////////////////////////////////////////////
// New property service
var SCRIPT_PROP = PropertiesService.getScriptProperties();
function doPost(e) { return handleResponse(e); }
function handleResponse(e) {
// Get public lock, one that locks for all invocations
// (https://gsuite-developers.googleblog.com/2011/10/concurrency-and-google-apps-script.html)
var lock = LockService.getPublicLock();
// Allow the write process up to 30 seconds
lock.waitLock(30000);
try {
// Generate a (not very good) UUID for this submission
var submissionID = e.parameter.id || 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
return v.toString(16);
});
// Open the spreadsheet document and select the right sheet page
var sheetName = e.parameter.sheet_name || 'Sheet1';
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(sheetName);
// Get column headers from spreadsheet
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 available row
var row = [];
// Loop through the column headers and find matching fields from the form submission to populate a new row
for (i in headers) {
if (headers[i].toLowerCase() == "timestamp") {
row.push((new Date()).toISOString());
} else if (e.parameter[headers[i].toLowerCase()] !== undefined) {
row.push(e.parameter[headers[i].toLowerCase()]);
} else if (headers[i].toLowerCase() == "id") {
row.push(submissionID);
} else {
row.push('');
}
}
// More efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// Return result in JSON
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow, "id": submissionID}))
.setMimeType(ContentService.MimeType.JSON)
;
} catch(e) {
// If an error occurs, return it in a JSON wrapper
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON)
;
} finally {
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
document.querySelectorAll('form[data-gsheet]').forEach(elForm => {
const inputs = Array.from(elForm.querySelectorAll('input, button, select, textarea'));
const gsheetURL = elForm.dataset.gsheet;
elForm.addEventListener('submit', submitEvent => {
submitEvent.preventDefault();
submitEvent.stopPropagation();
const data = new FormData(elForm);
const query = new URLSearchParams();
Array.from(data.keys()).forEach(k => query.set(k, data.get(k)));
const reqData = { method: 'POST', body: query };
inputs.forEach(elInp => elInp.setAttribute('disabled', 'disabled'));
fetch(gsheetURL, reqData)
.then(resp => resp.json())
.then(data => {
if (data.result === 'success') {
inputs.forEach(elInp => elInp.removeAttribute('disabled'));
elForm.classList.add('survey--done');
setTimeout(() => elForm.classList.remove('survey--done'), 1500);
} else {
alert(data.result || data);
}
})
;
});
});
@joleroi
Copy link

joleroi commented Aug 1, 2019

Is there any reason not to use Utilities.get_uuid in order to get a UUID?

@jasonestewart
Copy link

Setting the project's triggers is now under Edit > Current Project's Triggers

@NiK0lach
Copy link

Hi there, is a way to automatically run a function as form content is delivered to sheets,like when using Google forms uses a trigger form activation ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment