Web App Development with Google Apps Script/Data from spreadsheets
Structure overview
[edit | edit source]Google Apps Script lets you have access to Google Sheets. You do it by making a connection first to the Google Drive Sheet. There are two ways to do this:
ss = SpreadsheetApp.openById(id)where "id" is the typical google drive id (the long string of characters usually right after "https://docs.google.com/spreadsheets/d/" in the url of a sheet).ss = SpreadsheetApp.getActive()if your script is tied to a Spreadsheet (see below)
Once you have the spreadsheet variable (ss in those examples), you can access the various sheets (some people call them tabs) in that doc and any data inside them.
Script tied to spreadsheet
[edit | edit source]If you want you can tie your script to a spreadsheet. Then the script doesn't live as a stand-alone google drive file but rather is stored with the Google Sheet. You can access the script by going to Tools->Script editor. It'll then look just like any other script. Oddly it still needs a name, but it'll prompt you to name it when you try to run something.
If you have it connected to a spreadsheet you definitely want to use the second approach mentioned above. Interestingly, the first method always works. There are some subtleties to access control when you tie it to a spreadsheet. Personally I do this a lot because if I want to edit the script I usually want access to the data at the same time. This way they're all stored together.
Read the data from a spreadsheet
[edit | edit source]If you have the spreadsheet variable, let's say you wanted to get all the data in a sheet called "Sheet1", which is the default name for the first sheet in a brand new Google Sheet document. You'd do that like this:
var sheet = ss.getSheetByName("Sheet1");
var data = sheet.getDataRange().getValues();
// then do something with the data
This usually takes a second or two, even for large (500+ row) spreadsheets. Once you have the data you can do all sorts of things. Let's say you have a spreadsheet that looks like this:
| Name | age | |
|---|---|---|
| Andy | andy@myschool.edu | 48 |
| Ash | ash@myschool.edu | 20 |
| Char | char@myschool.edu | 18 |
Assuming it's in "Sheet1", you could augment the above code to create an html list to be displayed on the main.html page like this:
data.shift(); // this gets rid of the top row (the one with the headers in it)
var html = "<ul>";
html += data.map(r=>`<li>${r[0]} (${r[1]}) is ${r[2]} years old`).join('');
html += "</ul>";
t.html=html