HOWTO: 

Create a Google Apps Script Web App bound to a Spreadsheet

INTRO

Note: Google has introduced a new script editor,  to publish/update a new version of your script (after initial deployment) is handled differently. See here how to do it in the new script editor: New Versions for New Google Script Editor 


For many reasons when connecting App Inventor to "Google" we may need to create a web app as an interface between our app and our Google things. Many people struggle with the concept and this howto is intended to help demistify the process. One of the main reasons for using a web app is that is is created using your own google account, and

therefore provides access to files on your google drive, overcoming authentication issues for users, especially if you allow then to run the script as "you". You can set up a web app in a standalone script project, but, more likely than not, you will be looking to access data on a google sheet, so it makes sense to create the web app as a bound project.


You can read all about google apps scripts and web apps here:

https://developers.google.com/apps-script/guides/web

and here a more detailed guide about web apps and google apps script by Tanaike

Taking Advantage of Web Apps with Google Apps Script


For this howto we will setup a simple web app that appends a row of data to your spreadsheet.

The same code will be used as if it were a standalone script

The data will be added to Sheet1

The howto will use HTTP GET, this means you can also test your web app in a browser.

SETUP

1. Open up / create your spreadsheet

In Sheet1 put "Name" in cell A1, and "Age" in cell B1. You don't have to use the quotes.

2. Click on Tools and select Script editor

A new empty script project will open

3. Save the project with a meaningful name

(File > Save)

4. Paste/type in your code, removing the default code already there

You can copy the code below...

SCRIPT

function doGet(e) { 

  // this function HAS to be called doGet(e) and it does need the e in the parantheses to carry the parameters

  //for a bound script you can also use: var ss = SpreadsheetApp.getActiveSpreadsheet();

  // You can find the FILE ID in the url address bar of the spreadsheet

  

  var ss = SpreadsheetApp.openById("<FILE ID HERE>"); 

  var sh = ss.getSheetByName("Sheet1"); 

  var name = e.parameter.name ; 

  var age = e.parameter.age ;


  sh.appendRow([name,age]);

  return ContentService.createTextOutput("Success: " + name + ", " + age + " added");

}


5. Publish your web app

If you make ANY changes to your script after publishing, you will have to publish again, with a new version.


When you publish for the first time, you will be asked to authenticate your account to use the app. Click through the prompts and then click on ALLOW.


Note

Who has access to the app:

This is important with AI2, because you are not connecting to the web app with a google account of any description, not even your own. Selecting "Only myself" or "Anyone (with a Google Account)" will not work, you have to select "Anyone, even anonymous" (in the new script editor, this is "Anyone"), which as it states, will allow anyone who visits the web app to run it.


6. Authentication

7. Grab the url for use in your app. 

(This does not change once your app is first published)

https://script.google.com/macros/s/AKfycbwQLBxaE0eDd2DiHMFPCmT0h9I1ArtuMVwneufExGQmeBSIEWOj/exec

8. Test the web app in your browser:

https://script.google.com/macros/s/AKfycbwQLBxaE0eDd2DiHMFPCmT0h9I1ArtuMVwneufExGQmeBSIEWOj/exec?name=John&age=35

Check back in your spreadsheet and you should see that John,35 has been added to the next empty row.

9. Test the web app in App inventor

You will use the web component to generate data in your spreadsheet. See blocks and screen below

BLOCKS

SCREEN

That should be it; how to setup a google apps script web app, bound to a spreadsheet. 

This will work for private and public spreadsheets on your google drive.