This tutorial is for Google Workspace Domain accounts.
Recently, I was a guest interviewee at a Google Workspace Developer Summit in Singapore and an attendee asked me how they could automatically add the editor’s email to an adjacent cell when the user checks a check box in Google Sheets.
This can be incredibly handy for project managers to see when a user completes a task or, at a glance, who edited a row.
Of course, there are some simple ways of doing this out-of-the-box within Google Sheets.
A simple right-click > Show edit history on any cell can reveal who has edited the cell in the past.
If you want a more detailed history of edits on your Google Sheet workbook then you can always select the version history button in the top right of your Sheet.
The ‘clock’ icon here opens the version history page.
The problem with these options is that it is not there on the screen for the user to quickly see who edited what line.
In this tutorial, we are going to use some Google Apps Script magic to automatically add the editor’s email to the row when they click that checkbox.
In this tutorial, we follow the adventures of Captain Webhook and his crew in their insatiable desire to report their latest booty conquest to me via Google Chat webhooks…
That, dear readers, did not come out right.
Webhooks are HTTP POST requests that are usually generated by a triggered event. The event could come from any third-party source within Google Workspace like:
When a user submits a form or clicks a button on a WebApp.
Patreon when another awesome supporter shows you some love.
Or when a rather rambunctious figment of my imagination insists on updating me when his latest haul of treasure has come in… live.
Sigh.
Chat App webhooks will need an intermediary step for them to be compiled in a way that the Google Chat API can understand. We’ve chosen Google Apps Script here to do this, but you can choose to use any other language to convert your data into Chat API readable JSON or even build in a CLI to post your webhook request.
You’ve probably come across the problem where you need to know when a piece of data has been added to your spreadsheet. You probably have been equally frustrated that there is no out-of-the-box function that will do just this.
You’ve tried TODAY() and NOW(), but they change dynamically. What you really need here is something that does not change.
Let’s look at two workarounds that can help you out with this problem.
If you have ever tried to get a list of all the child files and folders of a parent folder in Google Drive, you’ve no doubt discovered that it is a slow old process. Iterating over each item in a folder and then reading the metadata of that file or folder before calling the next one can take forever!
The built-in DriveApp Class for Google Apps Script is great for working on a small number of files and folders but it just doesn’t have the functionality to retrieve specific fields in your metadata, nor does the searchFiles method or searchFolders method have the ability to isolate just the fields that you want to retrieve. Subsequently, your processing time increases significantly as it ships junk data.
This tutorial is my attempt at, as the kiddies say today, creating a ‘blazingly fast’ file and folder iterator with Google’s Drive API v2 for Apps Script.
Table of Contents
Skill level: Intermediate
What We’re Building
Our goal for this tutorial is to extract all of the child files and folders in the directory tree of a parent folder.
To save some confusion later on we will call both files and folders, items. This is the same vernacular that the Google Drive API uses.
Once we have a list of all the items, we want to store them in a Google Sheet.
Our Google Sheet will contain the following columns:
Icon (File or folder emoji for a quick visual reference)
File Name
File ID
Parent Name (Parent folder, that is)
Parent ID (We’ll throw in a link to the folder here too)
File MIME type
So your Google Sheet will look a little like this: Now, we are going to sacrifice some code simplicity in exchange for speed in this tutorial and I recommend that you follow along to get a full understanding of the code and how it works.
Getting All the Google Drive Files and Folders in a Parent Folder with Apps Script
My first inclination here was to get a list of all items in the parent folder and then iterate over them calling the Drive API to retrieve each one. However, I knew that was frustratingly slow.
Instead, I thought I would use the Drive.Files.list() method. On its own, this will collect your entire list of files and folders in ‘My Drive’.
Not so surprisingly though, the process is pretty quick for the amount of data it is shipping. Why? Because it is making the query server-side in one big hit and then returns everything back.
Cool. I was on the right track, but I didn’t want all of my files and folders. I only wanted to collect the items starting from a selected parent folder.
Query the Drive API files list
Fortunately, one of the optional parameters of the ‘list’ method is ‘query’ represented as (q).
Great! So how do I make a query? Well, there was no link from the query definition to any documentation on this. C’mon, Google!
Scrolling down the sidebar in the docs did eventually reveal a list of Search Query Terms. The ‘parents’ query looked promising.
After a bit more stumbling around I came across a guide in the docs called ‘Search for files & folders’. Here there was a more concrete example of how to use the query.
"q" = "'[folder ID]' in parents"
Replace [folder ID] with your folder ID, but make sure you keep it in single quotation marks.
I also discovered a while ago that it is a good idea to add 'trashed = false' to ensure that any recently trashed files are not included in our list.
Stay classy my friends.
Cool. I think we have enough now to run our first test.
Add the following to your Test.gs file.
Get folder list items from the Drive API v2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
functiongetFolderItems(folderId){
constpayload=
{
'q':`'${folderId}'inparents andtrashed=false`,
};
returnDrive.Files.list(payload);
// return Drive.Files.list();
};
// run this funciton to test.
functiontest_getFolderItems(){
constfolderId="[folder ID]";// Add your folder id here.
constresp=getFolderItems(folderId);
console.log(resp)
};
Update the folder ID in test_getFolderItems() and run the function.
Your results should look a little like this:
Returned JSON response from Drive Files List for Google Drive
"nextPageToken":"G2soidiU2vYadcii#$-2334",// Appears if you have more than 100 items in your folder.
"items":[
{<em>item metadata</em>},
{<em>item metadata</em>},
{<em>item metadata</em>},
{<em>item metadata</em>},
...
]
}
It’s unlikely that you will be able to see everything in the returned object from the log. If you do want to see it all, you could store it in a JSON file like this:
Create a JSON file with response data.
1
2
3
DriveApp
.getFolderById([Destination folder id])
.createFile("Folder Item Object.json",resp)
Here you can see that the returned response contains a bunch of information about the request followed by an array with the property id, ‘items’. This will be where all of the files and folder data are stored.
Another noteworthy property is the "nextPageToken". This may not appear for you in your selected folder it only appears when the request reaches the maximum number of results it can return. By default, this is 100 results, but you can modify this to display fewer with 'maxResults' = [integer] parameter.
This will become useful for us later.
Looking at the item object metadata
Items are stored in an array of objects containing all the metadata for that object. Let’s take a look at an example. I’ll highlight all the parts that we will need to extract for our Google Sheet.
"title": "Basic Sheet and Notes Pages Template w/script"
"id": "k2hlho34IamAgoat99two"
Get only selected fields from the Google Drive item with Google Apps Script
It’s great to get all the data for each item and all but grabbing such a big hunk of information will slow down our code and, honestly, is a little confusing. Fortunately, we can request only certain fields to be displayed.
Now that we know the properties we want to extract we can include a ‘fields’ parameter into our payload of our request to the Drive API.
How do we write a ‘fields’ parameter string?
All fields that you want to be displayed in your string are separated by a comma.
"id, mimeType, title"
Any nested object fields, for example, "lastModifyingUser":{"picture": "URL"} are separated by a forward slash.
"lastModifyingUser/picture/url"
Any property within an array of objects is placed between parentheses “()“.
"parents(id)"
In our example, our root property is items, which is an array of objects. One of those objects, ‘parents’ contains another array of objects from which we want to extract the ‘id’. Our fields parameter would then look like this:
'fields': items(id, title, mimeType)
Let’s update our code:
Updated test code with fields included.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
functiongetFolderItems(folderId){
constpayload=
{
'q':`'${folderId}'inparents andtrashed=false`,
'fields':`items(id,title,mimeType)`
};
returnDrive.Files.list(payload);
// return Drive.Files.list();
};
functiontest_getFolderItems(){
constfolderId="[folder ID]";// Add your folder id here.
constresp=getFolderItems(folderId);
console.log(resp)
};
Let’s check out our results now:
Child items of a Google Drive folder with only selected fields
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
{
"items":[
{
"title":"Basic Sheet and Notes Pages Template w/script",
If you are running this script on Shared Drives, you might be getting an error. You will need to include the following parameters:
1
2
3
...
'supportsAllDrives':true,
'includeItemsFromAllDrives':true
You will also need to include supportsAllDrives in your main run function in the first folders.name object. More on this later.
Adding Multiple ‘[folder id] in Parents’ queries – the secret sauce!
This is the secret sauce. It will become more apparent when setup the folder iterator later but essentially the reason why our script will be so fast is that we can bank a bunch of folder ids in our query and search multiple folders at one time returning each of the folder’s child items.
This was a pretty cool epiphany for me.
We will abstract out the query to a query builder function and update our getFolderItems and test functions.
Add the test function to your Test.gs file and replace the Code.gs with the two new functions. Make sure you keep the top comments in Code.gs so you know where to go to get help when you come back to it in the future, 😉.
The Test Function: test_getItemsForFolderArray()
test_getItemsForFolderArray
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
functiontest_getItemsForFolderArray(){
constfolders=[
{
id:"[Your Folder ID 1]",
name:"[Your folder name 1]"
},
{
id:"[Your Folder ID 2]",
name:"[Your folder name 2]"
},
{
id:"[Your Folder ID 3]",
name:"[Your folder name 3]"
}
];
constresp=getCurrentDirectory(folders);
console.log(JSON.stringify(resp,null," "))
};
This time around, we are going to create an array of folders.
A bit of foreshadowing here, but we will want to store a dynamic list of parent folders that we want to query. Each array contains an object with an id property and name property.
Note that we have changed the function that we are calling to getItemsForFolderArray() adding our folders as an argument.
Get Items for All Folders in the Query: getItemsForFolderArray()
getItemsForFolderArray
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
* Retrieves the current list of items from the selected folders from the Drive API.
First, notice that we have renamed the getFolderItem() function to getItemsForFolderArray() to more clearly explain what it does.
On line 17, we have replaced the text query with the variable, “queryString” which can be found on line 13. This variable calls the function createQueryString(folders), taking the folders variable as an argument.
Line 13: We start off by creating an empty let variable called “queryString”.
We then need to check two conditions of the incoming folders.
Lines 15-17: If there is only one folder in our ‘folders’ array then we just need to input the folder id along with the ‘in parents’ query.
Lines 19-25: Alternatively, if there is more than one folder in our ‘folders’ array we need to make an ‘or’ statement.
Lines 20 & 24: All ‘or’ statements are batched within parentheses if they must be resolved before another ‘and’ query. For example:
' (id1 in parents or id2 in parents) AND trashed=false'
Line 21: Next, we start a forEach loop to iterate through each of your folders. We also check to ensure we have the index (idx) for each iteration.
Line 22: On each iteration, we first check if we are on the last folder. If we are, we don’t need to append an “OR” to the end of our ‘in parents’ query. However, if with aren’t then we add the “OR” statement.
Line 28: Next, we add any other queries we want to add to our query statement.
Line 31: Lastly, we return our built query string back to the getItemsForFolderArray() function to be added to the payload send to the Google Drive API.
Run the script
Go ahead and run test_getItemsForFolderArray() again.
Notice now that when you run the code, you will see different parent ids in your item list.
Building the 2d file and Folder Array for the Google Sheet and Updating the ‘folders’ array for the next search
Let’s create a final assisting function before we write our main function. This one is a bit of a doozy.
Our goal is to iterate over the newly collected items from the Drive API call and on each iteration carry out two things:
Create an array of data for each column of the target Google Sheet.
Update the ‘folders’ variable array with any new child folder that we have collected from the recent API request.
Why do these two steps in one function?
Well, you could certainly abstract them into their own functions for clarity, but both functions do need to iterate over the same data, so rather than wasting precious time with two iterations we will run one iteration and extract the necessary data for each array in one hit.
I’ll go into more detail on both of these arrays in a moment, but first, let’s take a look at the code for this function.
createFileArrays
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
/**
* Iterates through all found items and creates two arrays:
* 1) spreadsheetFormatted - A 2d array to be added to the selected sheet tab.
The createFileArrays function takes two parameters, the folderArray retrieved from the Google Drive API call and the current folder array that was used in the query of the current API call.
Setup and Loop
Lines 13-16: The first task is to set up the fileArrays object that we will return at the end of the function. This object contains:
spreadsheetFormatted: This is the 2d array that will populate the target Google Sheet. You can see how it is structured in lines 32-37.
childFolderIds: This is the collection of any folders found on the current call to the Drive API.
On each iteration, these properties will be updated.
Line 19: Here, we use a JavaScript forEach loop to iterate through each of the found items in the selected folders.
Line 21: We will also check the mimeType (the file type) on each iteration to see if it is a folder and if it is, set isFolder to true. This will be referenced in two places later on in the function.
Creating the spreadsheet formatted array
Line 25: As a part of our 2d array for the destination Google Sheet, we will display the parent id and parent name of the file. The tricky part is that in Google Drive each file can have multiple parents. We will need to create a simple array of all parent IDs of the file and iterate over them to compare them against the list of folder IDs.
Line 26: Next, we will use the JavaScript ‘find’ method to search for any id that is included in the array of file parent folder Ids. This will return the matching parent folder (with its id and name) if it is found or a falsy ‘undefined’ if one does not exist in the list.
The results will then be used in the spreadsheet array.
It is undoubtedly improbable for the current list of folder ids to not be in the parent list of ids for the currently iterated items. This is because they were extracted from the ‘folders’ list.
Line 28: For the parent folder id column of our Google Sheet it would be helpful to provide a hyperlink to the folder where the item exists.
Here, we use the HYPERLINK Google Sheets function applying the standard Google Drive URL with the appended parent folder ID and then using the folder ID as the label.
Line 30: Next, we create either a file emoji or folder emoji for our image cell using the isFolder boolean variable generated on line 21. This will provide a convenient visual queue to identify the nature of the item.
Lines 31-38: Now that we have all the components we need to build our array we set them into the fileData array variable.
Line 40: Finally, we concatenate the newly created array to the existing fileArrays.spreadsheetFormatted array.
Creating the child folders list
Every time we search over our Google Drive’s selected IDs it is likely that we will collect more folders to search. In this section of the function, we need to create a new array of folders from the items we have just collected.
Lines 44-51: If the currently iterated item is a folder (isFolder) is set to true, then we need to add it to our fileArrays.childFoldersIds array.
Line 5: Out of curiosity, we want to check to see how long it will take for the script to run. We use the JavaScript time method with the console here. We will add the end-time method at the end of the script.
Line 8: Here we add our parent folder id. Go ahead and update the script to add your own folder id now.
Line 10: The folderData object variable will store the response from the Drive API and will be updated after each call to the API.
Line 11: The directoryArray variable stores the total found items in the directory tree as a 2d array to be added to the destination Google Sheets. It will be concatenated on each iteration of the while loop.
Lines 12-17: We are already familiar with the folders variable in our testing of the helper functions. Here we set up the parent folder as our first folder to search. Adding its array to the id.
For the name, we call the Drive API once using the ‘get’ method. We specify the field “title” only and then retrieve the title of the folder. Alternatively, you could paste in the folder name yourself and save a call to the API.
Note that if you are using Shared Drives you will also need to include "supportsAllDrives": true to the parameters to request the folder name. So folders.name should look like this:
So as I alluded to earlier, we aren’t actually, calling the Drive API list method on each folder. Instead on each call to the API we collect a list of folders and add them to a folders list then we will run a query on all of the folders in our list (array).
Line 20: Starts the main while loop. Here we check if there is a least one folder item in our folders array, by checking the length of the array. If there is, then we continue.
Remember, the folders array is updated every time we make a list request to the Google Drive API. So when our array runs out of folders, then we can stop the while loop.
Line 22: Here we call the getItemsForFolderArray() function taking the folders array as an argument. This will call the Drive API and return the current list of items found in all of the folders in the folders array and store it in the folderData object.
Line 23: Remember, that the folderData object contains data on the Drive API list process. This includes the item property which is an array of all found items from the list query. We will temporarily store these items in the items array.
Our next task is to store both our data that is going in the Spreadsheet and also update the folders array.
Line 27: First, we call the createItemsArrays() function applying the items and folders arrays as arguments. This will return the childFolderIds array and spreadsheetFormatted array assigning it to the variable itemArrays.
Line 28: Next, we replace the folders array with our new list of folders to query.
Line 29: Then, concatenate the directoryArray with our newly found list of items.
Updating the Google Sheet with our Items List
Our last major task for this main run function is to update our Google Sheet.
Line 36: We then need to select a range of cells to add our data. This will be equal to the number of columns and rows in our 2d array. Here we call the getRange() method which has an optional 4 integer insert parameter setup:
Start Row: This is row 2 below our header.
Start Column: This is Column A or column number 1.
Row depth: How many rows we will be adding our data to. We can calculate this by getting the length of the main, outer, 2d array of the directoryArray.
Column width: The number of columns across on each row we will be adding. To get this value, we can get the length of the zeroeth item in the inner array.
Line 37: Finally, we add our array values to the Google Sheet range with the setValues() method adding the directoryArray as its argument.
Run your code
After updating your parent folder and saving your script, you should be good to go running your code and see the results.
Go ahead and check out your results in your connected Google Sheet!
What about edge cases?
By now some of you may have been vigorously rage-correcting your glasses thinking, ‘This code sucks! Yagi hasn’t even handled for huge amounts of items in a folder! What happens if you are querying a large number of folders all at once?! Will it break the query? What if the code times out?! Argh!^n.”
Well first, that’s a bit rude mate. Second, now that we have our basic structure sorted out let’s go back into our code and update it one at a time.
One thing I will leave out is handling for time-outs. It’s a big subject with many approaches. I discuss this a little more later when we get to it.
Next Page Tokens: Handling for a large item list
The Google Drive API file list is limited to returning a maximum of 100 items per request. I guess the list method is considered a batch request because it contains limitations in batch requests.
Fortunately, each list request can provide a nextPageToken property with a unique id that you can add to your next API request and get to the next page. If the total number of queries is less than the maximum number of requests then the nextPageToken property will not be present.
With this in mind, we can go back and update the getFileAndFolderIds() function and getItemsForFolderArray() function.
getItemsForFolderArray()
Our first task is to retrieve the page token from the Drive API query.
Check out the updated getItemsForFolderArray() function:
getItemsForFolderArray
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/**
* Retrieves the current list of items from the selected folders from the Drive API.
Lines 8 & 12: You can see now we have an extra parameter. The pageToken. This will either be a token string for the next page or null.
Line 19: Next, we need to include the nextPageToken field into the fields property of the payload to ensure that it is returned when it is available.
Line 23: Lastly, we check if we have a page token, for the next page. If we do, then we know that we are looking at the next page of the current query of folders not a new query of folders. We then add the page token to our payload to be sent as a request to the Drive API.
getFileAndFolderIds()
Now we need to create a loop whenever we have a page token to iterate over the current query of folders. Check out our updated code:
getFileAndFolderIds for nextPageToken
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/**
* Main run file that retrieves all child files and folders in a parent folder.
*/
functiongetFileAndFolderIds(){
console.time("getFilesAndFoldersIds")// To check how long we take.
Lines 21 & 33: First, we create a JavaScript do-while loop. We will need to carry out the first request to the API before we start the loop so we need to make a ‘do’ statement first. Then, while a page token exists, we need to continue to call the API until we have run out of pages.
Line 22 & 29: Next, we moved out our items array and called it before this do-while loop. This way we can append to it and build up the items array with new items as they come in from the consecutive page requests.
Lines 10 & 31: Lastly, we add a let variable, pageToken, that we update on line 31. If the page token is retrievable, it will return a token string otherwise the variable will store null.
Testing the page token iterator
We can test the page token iterator by adding the maxResults parameter to the payload variable in the getItemsForFolderArray() function.
Try setting the max results to something really small like 5 or 10. Like this:
'maxResults' = 10
Now run the main getFileAndFolderIds() script and note how long the script took. It should have taken quite a bit longer. Don’t forget to remove this line when you are done testing.
Another way of reviewing this process is to use the Apps Script IDE debugger and mark the ‘pagetoken = line‘ you can then review the debugger data to see what data was collected.
Max Query Length: Is the query too long
With the way our query works, this edge case is less likely to occur but is possible enough that it is worth checking for.
Hypothetically, a query may have a maximum string length of n characters.
This isn’t something I had a chance to personally measure but the maximum query length may be a little under 8,000 characters if we can assume our API request behaves the same way as a URL query, well, according to the Improve Performance documentation. The Stackoverflowians have mixed views on this citing a query characters length of between 29949 and 29999 for one commenter and 7340 for another.
Probably our best bet here is to just set a variable with a max number of queries for ‘[folder is] in parents‘ that we can generate, and modify it in the future should we confront an error. Or, you know, test the length properly 🤷♂️.
Get the max number of ‘in parent’ queries
Add the following code to your Test.gs file to get the maximum number of folder queries we should add per request.
test_queryLen()
1
2
3
4
5
6
7
8
9
functiontest_queryLen(){
constrestOfQueryString="AND trashed=false"
constsignIdQueryString="'111111111111111111111111111111111' in parents OR "
constmaxQueryStringLength=29949// or possibly 7340
Basically, we are setting our maximum hypothetical query string length. Then we subtract the remaining part of the query from the length before dividing that value by the average length of a ‘[folder is] in parents‘ query.
This will give us the total number of folder queries we can add to a query request before it throws a wobbly.
With this value in hand, let’s update our code.
getFilesAndFolderIds() update to handle max query string length
getFileAndFolderIds() handle for max query length
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
functiongetFileAndFolderIds(){
console.time("getFilesAndFoldersIds")
constrootId="[Your parent folder ID]";// The main parent folder.
Line 4: First we set a maximum number of folders that we want to add to our query. You can see that I added ‘5’ above for testing, but I would return to ‘598’ for future queries.
Lines 16-18: Next, we create an array that will temporarily store any remaining folders that we did not have enough room to add to our query.
Lines 26-33: Before making our API request we check the total number of folders that we need to query. If the total number (array length) exceeds our maxNumOfFoldersPerQuery value then we need to store the remainder in foldersRemaining and query the rest.
Line 50-52: Once we have made our request to the API we update the folders array with the remaining folders and new folders that we collected with our query.
Go ahead and test the code again now, with a small number in the maxNumOfFoldersPerQuery variable. Then run the function.
You could also console log the remaining folders array to see what is left over.
Rest the maxNumOfFoldersPerQuery variable with the larger value in the comments or your own custom value.
Some Edgier Edge Cases
So this is about as far as I want to go in this tutorial, but if you want to go further into the weeds you might want to consider a few other edge cases.
Timeout
Roughly, your script will time out after about 6 minutes of running this script. So far, I have yet to have a problem with the script timing out on large directories due to its solid performance. However, on a less performant version, I did and had to handle for it.
Some alternatives and workaround you might want to consider are:
Monitoring the time and triggering a clock trigger to run the remaining folder iterations just before the time runs out.
According to the docs, Google Drive API requests are limited to:
20,000 queries per 100 seconds.
20,000 Per 100 seconds per user.
That is an awful lot of queries.
Conclusion
“…blazingly fast…”, Jeff Delaney, FireShip.io
As you can see the code we build together is a, dare I say, ‘blazingly fast’, approach to a glacial problem of retrieving files and folders from a Google Drive.
I really love to hear how you used the code in your own project. Did you extend it further to run bulk updates or deletes? Did you add new fields? Did you use it to duplicate a directory? Let me know in the comments below!
Need help with Google Workspace development?
My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Google Apps Script: WebApp, HtmlService, LockService; Google Sheets
In this tutorial, we are going to create an interactive story chain app that we can embed into a Google Site or your own site like WordPress.
What’s a chain story, Yagi?
Maybe you did this in school. Someone wrote the first part of a story. You then gave that story to someone else to continue writing. They then pass the story on to someone else to write the next part. And so on and so forth. In the end, the story is read out and everyone laughs at the direction the story went – except that one kid silently raging over their lack of control of the narrative.
Why are we making this? How’s it going to help me?
Well, for one, I thought it would be fun. More importantly, this will allow us to have a look at how Google Apps Scripts communicates client to server-side and vice versa in a little more advanced environment than our previous tutorial. It will also give us an opportunity to look at some more parts of Google Apps Script as they relate to creating a WebApp.
Our chain story WebApp tutorial will also give us an opportunity to look at some of the pitfalls of using WebaApp. Particularly when using the execute as me permissions. Finally, this will then launch us into our follow-up tutorial on updating the WebApp to execute as the user rather than me, the owner of the app.
This tutorial is the second part of the WebApp series. However, if you can read a bit of JS, CSS and HTML, you should be able to follow along and if you get stuck you can always go back to the first tutorial:
Embedded below is our interactive Chain Story web app. If you are feeling creative, read the story so far and then add your part to the story. It has been written by readers just like you: