By Duncan McBride
Introduction
Google App Script is a service from Google that allows you to write custom code which interacts with a Google file. This tutorial will mostly cover using App Script with a Google Sheet (henceforth sheet or spreadsheet).
The main goal for our use case is to make spreadsheet submissions accessible to anyone without making the spreadsheet itself public. How? By setting up an endpoint our main project can use to POST new form data. The data is sent to the endpoint via http and our code adds it to the spreadsheet just how we want it. This way we can record user input with reduced risks of griefing and malicious use. With App Script, you don’t have to worry about hosting; Google takes care of that.
Set Up
To get started, make a new Google Sheet. In each column of the top row of this spreadsheet, write the name of a field you’d like to collect. For example, we could put (Name, Email, Phone Number) in A1, B1, and C1 respectively. Next we’ll connect this spreadsheet to App Scripts. In the top menu bar, select Extensions -> App Script. This will open a new App Scripts project.
Get and Post Endpoints
For our use case, we need two functions, one for sending data and the other for receiving it. Here’s the javascript for a basic example.
// The default spreadsheet name is Sheet1, but if you have a different name, update it here.
const SHEET_NAME = "Sheet1";
/**
* POST requests - This adds a new row with the new data.
*/
function doPost(e) {
try {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
// 'e.parameter' contains the fields from the FormData
// If you have more/different fields, change this array.
const newRow = [
e.parameter.Name, // Column A
e.parameter.Email, // Column B
e.parameter['Phone Number'] // Column C ];
// Take the new data and add it to the sheet
sheet.appendRow(newRow);
// Return a success JSON
return ContentService.createOutput(JSON.stringify({
result: 'success',
row: sheet.getLastRow()
}))
.addHeader("Access-Control-Allow-Origin", "*")
.setMimeType(ContentService.MimeType.JSON);
} catch (err) {
// Return an error JSON
return ContentService.createOutput(JSON.stringify({
result: 'error',
message: err.message
}))
.addHeader("Access-Control-Allow-Origin", "*")
.setMimeType(ContentService.MimeType.JSON);
}
}
// GET handler, for sending data without giving sheet permissions.
function doGet(e) {
try {
// Get the target sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
const allData = sheet.getDataRange().getValues();
// Filter for the first 3 columns
const filteredData = allData.map(function(row) {
return row.slice(0, 4); // Gets columns 1-3
});
// Return the data as JSON
return ContentService.createTextOutput(JSON.stringify(filteredData))
.setMimeType(ContentService.MimeType.JSON);
} catch (err) {
return ContentService.createTextOutput(JSON.stringify({ error: err.message }))
.setMimeType(ContentService.MimeType.JSON);
}
}
You should read through this code and understand what it is doing. The most important thing to check is that you are grabbing the correct number of columns for your use case. For a real life application, here’s how we implemented the App Script for Croak Counter: https://github.com/2025-UI-SP/SP-Croak-Counter/blob/master/backend.gs
Once you have the code set up, select the Deploy button in the top right corner of the window. Set a version description, set yourself as the executor, and make sure the app can be accessed by anyone. You’re all set on Google’s end! Once deployed, you’ll get a Web App URL like this (https://script.google.com/macros/s/AKfycbwNnbH2HoyJhjJszMdEADa2ePwjgDOWz57w0KQnh7j82QH6PMZbZAuaFvE8MM31u4HM/exec). This is your endpoint. Anytime you want external code to access your script, it will do so via a POST or GET request to this address. Speaking of which…
Client Side Call
How exactly do we use this URL? In our App Script, we set up two functions that deal with either a POST or GET request. Based on this, we can make a GET or POST request in our app. Here’s an example:
<script>
// Hook URL - This is where to put the Web App URL you got from your App Script deployment.
const SCRIPT_URL = "https://script.google.com/macros/s/AKfycby--HVEiiuzRa2mJFS4EWDokGN_Z9CQsjCyueQ070GKpPHmr0h4gJFX8Cu5Jgi21BZyOw/exec";
// Basic form handeling
const form = document.getElementById('myForm');
const statusMessage = document.getElementById('statusMessage');
form.addEventListener('submit', function(e) {
e.preventDefault();
statusMessage.innerHTML = "Sending...";
const formData = new FormData(form);
fetch(SCRIPT_URL, {
method: 'POST',
body: formData
})
.then(response => response.json())
.then(data => {
if (data.result === 'success') {
statusMessage.innerHTML = "Success! Data added to row " + data.row + ".";
form.reset();
// Upon form submit, reload data
loadData();
} else {
statusMessage.innerHTML = "Error: " + data.message;
}
})
.catch(error => {
console.error('Error:', error);
statusMessage.innerHTML = "Error: Could not send data. " + error.message;
});
});
// Run this function when the page loads
window.addEventListener('load', loadData);
function loadData() {
document.getElementById('loader').style.display = 'block';
document.getElementById('data-container').innerHTML = '';
// Fetch data from .gs 'doGet' function
fetch(SCRIPT_URL)
.then(response => response.json())
.then(dataRows => {
// Error catching
if (dataRows.error) {
showError(dataRows);
return;
}
document.getElementById('loader').style.display = 'none';
const container = document.getElementById('data-container');
// Basic HTML table
let table = '<table border="1" cellpadding="5" cellspacing="0">';
dataRows.forEach((row, rowIndex) => {
// Use <th> for the first row (header), <td> for the rest
let cellTag = (rowIndex === 0) ? 'th' : 'td';
table += '<tr>';
row.forEach(cell => {
table += `<${cellTag}>${cell}</${cellTag}>`;
});
table += '</tr>';
});
table += '</table>';
container.innerHTML = table;
})
.catch(error => {
console.error('Load Data Error:', error);
showError({ message: "Could not fetch data. " + error.message });
});
}
function showError(error) {
document.getElementById('loader').innerText = 'Error: ' + error.message;
console.error('Apps Script call failed:', error);
}
</script>
This bit of JavaScript can run in a website external to the App Script. By using GET and POST, it can interact with our deployed App Script code. Of course, your use case may be slightly more complicated, but the basic GET and POST system is the same. Here’s how we implement it in Croak Counter:
https://github.com/2025-UI-SP/SP-Croak-Counter/blob/master/src/pages/Observations.jsx
Look (or ctl-f) for “const doUpload”, which is our upload function. At the time of writing, it’s around line 248.
Summary
App Scripts is fairly powerful. There’s quite a bit more that you can do with the service (documentation below). Do be aware that these endpoints can still be abused; it’s worth checking the input data every once in a while.