Saving Data From Your Web Pages to Google SheetsKent SmothermanBlockedUnblockFollowFollowingMar 11One question I get frequently from my front-end web development students at Interface Web School is how to collect information from a contact or signup form on a site they are working on for their portfolios.
In my full-stack class students are taught how to do that themselves, but new front-end developers need a simpler, more convenient way that doesn’t require learning how to do back-end development.
Fortunately, such developers with basic JavaScript skills can just use Google Sheets to get the job done.
Google Sheets can be scripted using Google’s Apps Script API, which is just some provided JavaScript functions that know how to manipulate various Google apps like Sheets and Calendar.
You can write some simple JavaScript using the API to receive form data from your web page and add it to a Sheet, for example — you just have to know where to send it so that it gets to your script.
First, the script: In the location of your choice on your Google Drive, use the New button, then hover over More and select Google Apps Script.
You should see a new script editor open, like this:Near the upper-left corner is the name of this script, which is by default Untitled project.
Click on this and give your script a name, like Sheets Script.
In the editor where you see the function myFunction, delete everything and paste in this script:function doPost(e) { try { //get id of sheet to append to var ss = SpreadsheetApp.
openById(e.
parameter.
sheetId); //get name of sheet to append to var sheet = ss.
getSheetByName(e.
parameter.
sheetName); //get header text var headers = sheet.
getRange(1, 1, 1, sheet.
getLastColumn()).
getValues()[0]; //get where to append row var nextRow = sheet.
getLastRow()+1; var row = []; //loop through the header columns to put the incoming data into the right column //A column with the heading Timestamp generates the current timestamp for (i in headers) { if (headers[i] == "Timestamp") { row.
push(new Date()); } else { //Put the data into the right column row.
push(e.
parameter[headers[i]]); } }//Put the new row into the sheet sheet.
appendRow(row); //Generate a JSON resopnse return ContentService .
createTextOutput(JSON.
stringify({status:true})) .
setMimeType(ContentService.
MimeType.
JSON); } catch (e) { return ContentService .
createTextOutput(JSON.
stringify({status:false})) .
setMimeType(ContentService.
MimeType.
JSON); }}//Write sheet.
appendRow(row); return ContentService .
createTextOutput(JSON.
stringify({status:true})) .
setMimeType(ContentService.
MimeType.
JSON); } catch (e) { return ContentService .
createTextOutput(JSON.
stringify({status:false})) .
setMimeType(ContentService.
MimeType.
JSON); }}You are now ready to publish this script as a web app.
Click the Publish menu option in the script editor toolbar at the top, then select Deploy as web app… to see:Be sure to select Me for Execute the app as and Anyone, even anonymous for Who has access to the app.
Copy the contents of Current web app URL to use in the next step.
Finally, click Deploy (or Update as shown above if you have made a change to the script since the first deploy).
In your web page, you’ll need a <form> with the correct form elements for the data you want to save in the sheet.
The name attribute of each form element should match a column heading in the sheet.
So something like this very simple form:<form id="myForm"> <input type="text" name="Name" placeholder="Full name"><br> <input type="text" name="Email" placeholder="Email"><br> <input type="text" name="Subject" placeholder="Subject"><br> <textarea name="Message"> <br><button id="submit" type="button">Submit</button> <input type="hidden" name="sheetId" value="25UN2Md9FNy23XWIgIuW-xFQUV29sznApnLDYbWaOLNZ"/> <input type="hidden" name="sheetName" value="Sheet1"/> </form>Note that each form element name matches a column heading in this sample sheet:There are also hidden inputs to hold the Google sheet id named sheetId, and another for the sheet name within this sheet, named sheetName.
When you are editing a sheet, this id can be found in the browser address bar, shown in bold in this example:https://docs.
google.
com/spreadsheets/d/25UN2Md9FNy23XWIgIuW-xFQUV29sznApnLDYbWaOLNZ/edit#gid=0Finally, you can use jQuery to send this information to your script with something like this:$.
ajax({ url: "https://script.
google.
com/macros/s/BKfycbwg0202_R6F19QZkK96YHpAj7z4BNGiz-rfGGYIR_Wc7DScl5z/exec", method: "post", dataType: "json", data: $("#myForm").
serialize(), error: error, success: handleResponse });Putting everything together in an HTML file, here is the complete example:<!DOCTYPE html><html><head> <title>Sheet Example</title> <script src="https://ajax.
googleapis.
com/ajax/libs/jquery/3.
3.
1/jquery.
min.
js"></script> <script> $(function() { $("#submit").
click(sendData);function sendData() { $.
ajax({ url: "https://script.
google.
com/macros/s/BKfycbwg0202_R6F19QZkK96YHpAj7z4BNGiz-rfGGYIR_Wc7DScl5z/exec", method: "post", dataType: "json", data: $("#myForm").
serialize(), error: error, success: handleResponse }); } function error() { //do something with error alert("Error submitting request"); } function handleResponse(data) { if (data.
status) { //report success alert("Data submitted"); } else { //report failure alert("Could not save your data"); } } </script></head><body> <form id="myForm"> First Name:<br> <input type="text" name="FirstName"><br> Last Name:<br> <input type="text" name="LastName"><br> Email:<br> <input type="text" name="Email"><br> <br> <button id="submit" type="button">Submit</button> <button id="get-calendar" type="button">Get Calendar</button> <input type="hidden" name="sheetId" value="25UN2Md9FNy23XWIgIuW-xFQUV29sznApnLDYbWaOLNZ"/> <input type="hidden" name="sheetName" value="Sheet1"/> </form></body></html>Note that the sheet id and script URL in this example are not actual values, just samples.
Your sheet id will come from your sheet, and the url in the $.
ajax call in the function sendData is the one you copied when you deployed the Google Apps Script.
Also note that since the URL of the Google Apps Script is a secure (HTTPS) link, your web page needs to also be hosted on a secure page with HTTPS as well.
That’s all there is to it!.This same script can be used to update any Google Sheet you need, each from a separate form and web page.
If you’d like an email notification when a new row has been added to a sheet, just enable the Google Sheet notification for that sheet using the Tools menu.
Happy developing!.