Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets

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:

Google Apps Script: How to create a basic interactive interface with Web Apps

Let’s get started…

The Example: An interactive chain story

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:

Go on! Read the story and give it a try!

What’s going on?

So what’s happening here? Let’s break it down into 3 main parts:

1. When the WebApp HTML loads.
2. User interaction.
3. Sending away the data and returning it.

When the WebApp loads

When the WebApp loads either from an embedded file or from the direct URL, the app will immediately try and call server-side and get the current story data. It will retrieve how many contributions there are and display them in the secondary header(1). Then it will load the current story inside the grey box (2).

A text area will come up in blue (3) with some instructions above and a submit (4) button below that will be disabled until the user types in a minimum amount of text.

At the bottom, is some place-marker text where the user’s text will appear once they hit enter.

user interaction

The user’s sole task is to add to the current story, by inputting their text in the blue textarea.

We don’t want them to simply send us an empty string of story data, nor do we want them just to reply with something short. So we have disabled the submit button until they have reached our arbitrary minimum number of characters. We’ve also provided a helpful red colour to the text until the minimum character length has been met.

Once the limit is met, the submit button is enabled and the colour of the text returns to black.

On the other end, we have set a limit to how many characters we want them to write (kinda like Twitter). We help them keep track of their limit with a counter just beside the submit button. Again, if they haven’t reached their limit, the tracker count will be red and also if they are approaching their max limit the tracker will turn read.

Okay, this was just plain fun to code, but it does give you an idea about what to consider with UI design. 

When the user is done, they hit the Submit button.

Take a look at the video below of the process. Make sure you keep an eye on all the guiding queues for the user.

Note! If you see a cannot access page, you may be signed into multiple Gmail accounts in one browser instance. Try an close down those accounts and reload the page.

Upon clicking Submit

When the user submits the code. The text they just entered is sent along with the number of contributions to the story so far – remember, back up at that subheader at the top.

Now if the number of contributors does not match the current number of rows in our Google Sheet that we have saved our data to, then it will return and display an unsuccessful error instead of the user’s input.

This is because someone already got to replying to this portion of the story first.

However, if they were successful, it will return their story and collapse the textarea, submit button and helper text.

Neat and tidy, hey?

When the user gets to enter their story before someone else, their story is added to the next empty row of the attached spreadsheet. Check out the attached spreadsheet below.

Note that this embedded display will take a refresh and maybe a few minutes to display your story. 

Now that we know how it all works. Let’s take a look at the code…

The code

Code.gs

Index.html

Code breakdown

We’ll presume that you got some rudimentary JS, HTML and CSS knowledge or have been following along since the previous tutorial here. So that we can keep this short…ish. But don’t worry, we’ll highlight some of our changes and some interesting bits of code.

Code.gs

Global variables

This time around we need to set up some global variables that will provide the file ID and sheet ID to our Google Sheet where we are going to store our story data.

We’ll be keeping everything in a single column. So we will also reference column 1.

do get()

We mentioned in our previous tutorial, that all WebApps require a doGet() or doPost() function to initialise the WebApp.

Everything is pretty much the same here as in our previous tutorial. However this time around we want to be able to make our Chain Story WebApp more responsive to different devices. So on a PC, we want our app to look like this:

But on a mobile device, you might want it to look like this:

Now normally, you can achieve this with modern HTML by using the metatag viewport tag in your Index.html file with something like this:

However, I soon discovered that HtmlService does not handle meta tags within the assigned HMTL file.

Instead, it has Google Apps Script side addMetaTag method. This method takes two arguments, the name of the metatag and its contents.

getStory()

This function is run when the Index.html first loads. Its job is to collect all the story text we stored in our Google Sheet and send it back client-side for the user to see.

First, we use SpreadsheetApp to get our sheet by ID and then get the Google Sheet tab our story is on. We reference our global variables to do this. Lines 12-13

Next, we grab the last row with getLastRow() on our sheet object.  This will get the number of the last row containing text. We’ll display this later in our HTML as contributions. Line 15

Our range of text starts at the very top of our sheet tab and runs all the way down column A. We can use getRange() to select this range. getRange() can take a number of combinations of arguments, but for us, we are adding these three:

• row start: start of the row. In our case, row 1.
• column start: the first column in the matrix. In our case, column 1.
• number of rows: number of rows to select. Here we grab our lastRow variable to do this dynamically.

Once we have the range, we can then get the values with getValues. This will give us a 2d array like this:

Next, we use the Javascript map method to iterate through each row of story data. Line 21

While we are at it, we will add some HTML. We want to ensure that each row is on a new line so we use the <p> for paragraph tags and then we separate each row of story data with a horizontal line with our <hr> tag. Lines 22-24

Once all our story parts are set up, we join them together (line 25) and send them back client-side to be displayed for the user (Line 27).

grabText()

The grabTex() function is called client-side when the user enters their data and hits the submit button. It takes one parameter that is an array for two items of data:

• element[0] : Contribution – This is the displayed value of current contributors to the story. We will use this value to compare against the current row length of our spreadsheet.
• element[1]: Story – The current user’s contribution to the story.

The function attempts to append the current user’s part of the story to the Google Sheet. However, if it can’t it will return a false condition back client-side which will trigger a custom error warning. On a success, though, the function will return the string of story text the user added.

Protecting from shenanigans: LockService

Our chain story app is incredibly popular. So there might be a chance that our users are trying to submit their story and add it to our Google Sheet all at the same time. This is going to make our story really silly. Also, there is a good chance that because processes are so close, some of the new rows might be edited over because the script is getting called too quickly.

We can block or lockout users from running parts of the script with GoogleAppsScript’s LockService.

There are three types of Lock Service locks:

• Document Lock – Prevents a user on the active Google document like a Sheet, Slide, Doc from being able to concurrently use the selected section of code.
• Script Lock – Prevents another user from the user the same section of code at the same time.
• User Lock – Prevents the current user from running the same section of code in, say, a callback.

We’ll be using Script Lock in our case because our users are not on an active document, they are just adding to that document (Google Sheet).  Line 16

Prevent consecutive clicks of Google Sheets Image Buttons in with Lock Service and Google Apps Script

Next, we need to see if anyone else has engaged the lock on the script. This is done with the waitLock() method. This method will attempt to create a lock for the selected amount of milliseconds in the argument. Line 18

Let’s wrap this up in a Try…Catch statement so that if we can’t get a lock in our desired time, then we will catch the issue as an error and then return a false condition back to the client side. Lines 20-22

Setting up our variables for checking and adding data

Once we have our lock, we can then set up our variables to interact with our Google Sheet.

First, we assign our parameters to some readable variables marking the zeroeth element as our previousRowCheck and our next element as our story text. Lines 26-27

Then, just like in our getStory() function, we call our spreadsheet and navigate to the Google Sheet tab where we are storing our story. Lines 29-31

The highlander of the narrative

As we mentioned earlier, it would be a little silly to have more than one entry for the next part of the story. We can check to see if someone has already made a contribution to this part of the story by getting the last row again (while we have our lock on) and comparing the last row value with the previousRowCheck. Keep in mind that the previousRowCheck variable is the current number of contributions when the user first opened our WebApp.

We do all this with our if statement. Line 34

Finally adding the story to the Google Sheet

If we get a match between the previousRowCheck and the lastRow, then we grab the range of the first empty cell. We then use the setValue method to add the user’s text to the story.

We then return the story back to our Index.html to be displayed for the user.

Flush and release program

Before we return everything back to the client side, we should probably ensure that all of our Spreadsheet App executions have been completed before we hand things over to another user. Otherwise, we might get some more errors and overlaps again.

We can use the SpreadsheetApp.flush() method to complete any pending executions that need to be applied to the spreadsheet. Line 39

Once this is done, we can then release the lock from our LockService for another user to submit their data. Line 41

Too slow!

If the user was too slow and someone beat them to the story, we release the lock straight away and return false back to the client side so that a warning for the user will appear.

Index.html

In the breakdown of the Index.html, we will just highlight the parts relevant to interacting with Google Apps Script along with a few interesting tidbits of Javascript in our script tags.

We’ve put all our script down the bottom here because it is not really necessary to run the visual CSS and HTML aspects of the Index.html page and the load for the initial story may take a few seconds.

onFailure()

The onFailure function is ripped pretty much directly from the previous tutorial.

To recap, the function will run if there is an error when communicating with the server-side Code.gs code and is initialised from the withFailureHandler function.

This will give us an error notification that we have, for this tutorial returned to the results div in the Index.html. You might just want to report a generic error to your user so they don’t see your working parts too much.

addStory()

The addStory() function runs when the index.html file loads for the user. Down the bottom of the code snippet, you will notice the google.script.run class which we have set up to first run a failure handler and, if all goes well, we run our success handler. Lines 28-30

The google.script.run class will execute our Google.gs server-side getStory() function. This will collect the story to date from our Google Sheets file and also get the total number of contributors by counting the current number of rows.

The story and contributor count will be returned to the addStory() function as an array of two elements as the currentStory parameter.

addStory() first gets the contributions header and the story div  (Lines 13-14). These have placeholder values in them so that the users know data will be loaded into these spots:

It then simply inserts the contribution number and the story into their assigned locations. Lines 17-18

Character Counter

The character counter is just a fun little bit of code that helps with user experience (UX) and guidance. It monitors the textarea (ID: textInput) and records the length of the string after each button press or paste.

First, we grab the textInput text area and also the submit button. Lines 7-8

Then we need to add an event listener for and key up or past event inside the textInput. We do this quickly by mapping through each event and assigning it to textInput.

For each event inside our map, we want to do the same thing.

In our textarea element, we have set the maxlength and minlength attribute. We want to grab these values with getAttribue() so that we can find the difference between them and assign them to the dif variable. We’ll use this in a moment. Lines 13-16

Next, we want to get the number of available characters remaining by subtracting the max length by the current length of values in the textInput. (lines 18-21)

The counter is then updated with the current count remaining. Line 23

A few guiding rules

Our submit button starts its life off as disabled:

We don’t want the user to just return a blank string of texts or a few short words. We want some fire, some substance, some energy! As, such, we set our minimum work count to 15.

To give your story writer a bit of a heads-up that their characters limit is drawing to a close, we check to see if the remaining number of characters is less than 15. If this is true, then we set the colour of our counter to red. Lines 25-26

Alternatively, if the count is greater than the difference between the max and min length of the textInput, we want to enable the submit button for the user and set the text in the textarea to our regular colour. Lines 27-29

Finally, to take into account that our user might remove the text or delete it out altogether, we check if the character count is greater than our difference we want to disable the submit button again and turn our text back to our warning colour. Lines 30-32

sendData()

When the user has written their portion of the story and hit “Submit”, the sendData() function is run.

First, we grab all the relevant elements of the Index.html file by their IDs. Lines 11-16

Next, we want to pretty quickly hide the “Submit” button, so that our users can’t send multiple times. We do this by updating the button disabled attribute to true.

Preparing and sending our data server-side

Our main aim at this stage is to quickly get our user’s text and the contribution count server-side to our Code.gs script to work its magic.

On line 20, we grab the user’s story text. We quickly trim out the white space on either side of the text to tidy it up before storing it in our storyInput variable to send.

On the next line, we grab the contributor count to compare with the current row count to ensure that no one has beaten our user to the punch.

We then put our story input and row count into the valsToGas array to be sent server-side. Line 23

Then we head all the way down to the bottom of the function and attempt to send our data off to our server-side grabText() function in our Code.gs file. Lines 50-53

onSuccess()

By now, you should know that if we get a failure, it will be reported with the onFailure() function. So let’s move on to our onSuccess() function.

The server-side grabText() function will return the element parameter to the onSuccess  function as ether the user’s story in a string or the false condition if the user was too slow and someone else posted before them. Line 31

The first task that the onSuccess() function does is hides the submit button, text input and all guiding text that is wrapped up in the #inputInfo div. It does this using the style.display property. Line 33

Then we set up our error warning text if the false condition is returned from the server. Line 36

Next, we use a ternary operator to determine that if there is an element (a.k.a not false), then return the element otherwise return the error text. We pack this into a result variable and send it to the result div. Lines 39-40

Running your WebApp

In this WebApp we have deployed our script with the following settings:

Execute as

All users that access the app are running as me. This stops the user from getting OAuth requests. It also opens up the app for use for any user even if they don’t have a Gmail account.

There are some drawbacks to this approach that we will cover in our conclusion.

Who has access

Here we have chosen anyone including anonymous users. We want everybody to have a crack at our little app, so why not?

You can limit this how you like.

 

 

If you want to learn more about deploying your WebApp, check out the previous tutorial.

Conclusion

In this tutorial, we expanded on our basic WebApp and created a Chain Story app that allowed us to look more closely into how to manage your script with LockService and make our Index.html more device friendly by adding meta tags into our HtmlService.

We also looked at a sneaky way to compare the current Google Sheet row with the initial sheet row loaded. You might find this useful when designing something like a bookings sheet ? .

Nevertheless, WebApps is not all fun and games there are some drawbacks…

Some pitfalls

Executing as me

When I published my first post on WebApp selecting the Execute the app as: Me option set off an amazing Twitter discussion of the pros and cons, and performance in general of WebApp. You can dive into the discussion here:

The Twitter link.

The discussion along with the Google App Script group advice really helped me to understand what is going on and hopefully better consolidate the advice I received here.

The main issue that was raised is that Google limits the number of simultaneous executions to 30. This isn’t just for 30 simultaneous execution within one Google Apps Script. Rather it is for all your scripts.

So if you are running a bunch of time-triggered scripts, and half a dozen web apps all on the one account or your WebApp simply gets hugely popular – because, let’s face it, you’re a star – then you are going to get the following warning:

There are too many scripts running simultaneously for this Google user account.

And your scripts are going to fail to run.

This will make your clients sad.

Which scripts, Yagi? Which script?

Simply, the thirty-first script spun up to run concurrently. It could be anyone. It could be your web app, but it could equally be another app you are attempting to run at the time.

Will my WebApp users see me for the failure I am?

Yes.

You won’t be able to hide it. Your user is going to end up with a connection error.

How likely is this going to occur for little old me?

Hmm. Well, the very unsatisfying answer is that it depends.

However, if you don’t have much going on with other scripts in your account and you are not expecting a massive onslaught of users to your WebApp all at once, then I think you will be good.

If you are using it for a small sign-up form or something that reacher a couple of 100 to 1000 users that you don’t expect to sign up all at once, then you will probably be fine. Just remember it will eat into your simultaneous executions count.

What’s the alternative?

I’m glad you asked.

You could change execute as  “Me” to “Users accessing the web app”. This means that users will be running the codes as themselves and you will be drawing from the user’s set of runtimes so it will be inconsequential for them to run the code.

As you may have guessed from my previous comment, this does have its own set of issues, with authentication warnings and the demand for the user to log into their Google Account. We’ll talk about this in the next chapter of this WebApp series.

Not being able to capture user information

It might be a good idea for us to be able to capture the user’s information. For example, in our current WebApp we use the current contributor number that we gain from our initial upload and compare it with the row length when the user submits. Because this is client-side the users might be able to manipulate this count to ensure that their story gets in first. If we can get the user’s email or even a user-specific session code (I wonder if Goggle Apps Script has something like this? ? ), then we can register the start row to the user server-side and compare that with the current row once they send their story in.

What if we didn’t want users to be greedy, so we limit their access to adding to the story once a day? Or what if they have been using naughty words and we need to ban the cheeky little blighters? Well, we need some sort of record of their progress.

What if we are adding our web app to a Google Workplace paid domain account? We could use the user’s id to grab their personal profile information or something personalised.

Please, Please send me down this rabbit hole

There are a whole bunch of Google Apps Script developers much smarter than me who have explored and tested the performance of Web Apps. Here are a couple of links from the communities recent discussion on the topic:

• Concurrent processing in App Scripts: by Adam Brainysmurf Morris
• Adam Well’s response to How do I use LockService properly every time in Google Apps Script?
• Taking advantage of Web Apps with Google Apps Script: Dr. Kanshi TANAIKE

Also a big thanks to the following on their help with the discussion on this:

• Martin Hawksey – for getting the ball rolling.
• Pablo Felip for the valuable insight.

Using Google Sheets as a Database

Another problem is with using Google Sheets for a database. While our simple little chain story will never get the traffic required to really cause us problems we do need to acknowledge that at some point, Google Sheets is not a database. There are storage limits and it is a pain to try and shape it into a SQL or even a document-based NoSQL database.

It’s not designed to quickly render results from it nor carry our complex queries on the fly. There are hard cell limits to.

Just no! Bad! Don’t do it or the Google Apps Script-inati will set Eric Koleda on you to persuade you with utter logic and absolute kindness. You will melt. Trust me.

In the meantime check out Eric’s article on the subject:

• Why you shouldn’t use Google Sheets as a database

So what is this WebApp stuff good for then?

Thanks, Yagi! You’ve set me up for such a great fall. I spent all this time with you learning WebApps only to be told it’s useless!

Wow! Wow! Wow! Easy there, tiger. It’s far from useless.

First, it is a fantastic tool for prototyping the front end of a project or testing the waters of how a new project may get traction.

Also, if you know you are doing a small project, this is still for you. You can even use the “Execute as me” functionality with little concern.

One recent example I had from a reader was to do a custom sign-up form for free tennis club training for seniors at a small club. Google Form’s functionality was just not right for the task. Great. This is perfect. you shouldn’t have to wind up a server and create a special database for something like this. Just build the WebApp.

If you are running projects inside your Google Workspace. You could run an administration-paid account that you don’t have any code running in (or very little active code). You could then use this for a whole bunch of users before it quits on you.

You could even build up your Google Sheets as a mini database and then make a transition to something like Firestore down the track so you have less learning overhead at the start.

The Final Bit

In our next tutorial, we will take this chain story script and modify it for use as a user accessing the WebApp. This means we will also make some tweaks to improve collections of data and create some restrictions to help give everyone a contribution to the story.

My main hope for this tutorial is to help inspire you to create a WebApp and learn a few new skills through examples. I’d love to hear what you created after this tutorial so don’t forget to comment below.

If you enjoyed the tutorial please like the article so I know that I am doing a good job and if you want to buy me a cuppa there is a donate button on the top right of the sidebar.

If you are really digging these tutorials you can also subscribe – top right of the sidebar again.

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.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

~Yagi