AppInventor – Post Data to Google Sheet

Thought I would write this one up on here, can’t take any credit for it, as original idea posted on youtube (credits below), but if I have it here it is easier to grab and use when I need it.

I have previously worked up a solution using a Google Form to submit data to a google sheet from an AppInventor app, but this method uses a standalone google apps script to do the same, but without the baggage of the google form. We will need to:

  • Have a google account (we want to use)
  • Create a spreadsheet, and set this out with the columns we need
  • Create a google apps script
  • Create an App Inventor app with the correct blocks

Your google account will be the owner of the google sheet and the google apps script, this overcomes any permissions when posting data

Open your google drive and create a new spreadsheet, given it a memorable name, add the columns you will need for your data with headers for each. In my example the columns are on Sheet1, the headers are id and name. My screenshot already has some test data submissions added.

sheetPostToGoogleSheet

Copy the File ID for the google sheet, you will need this for the next stage

Back in your google drive, create a new Google Apps Script, and paste in this code, overwriting the default myfunction() {}. Make sure you now paste in the sheet file ID you copied earlier to the two places where it says: FILE ID HERE. Adjust your sheet and column header parameters if needed. This is where the mistakes creep in, so take care.

scriptPostToGoogleSheet

Now save your work, give permissions etc. and then publish this as a web app, ensuring that in the Who has access to the app: box, you select: “Anyone, even anonymous”. Once published, grab the url of your web app, you will need this for the next stage:

(this is a fake url)

Open up AppInventor, create a new project, drag out a couple of textboxes (or however many you need to match the columns in your google sheet (I have only two). Also drag out a Web component from Connectivity.

designerPostToGoogleSheet

Now create the blocks you need to make the Submit button work

blocksPostToGoogleSheet

Test the app, check the google sheet. Hey presto!
(If it doesn’t work, you may need to open up the permissions on your google sheet to “Anyone with the link”)

For adding further columns in spreadsheet and script, ensure blocks are updated and the script is republished

a2sblocks

Credits go to Android Labs for the original idea

and to Dominic on the AI2 google group for bringing this to my attention through his question.

11 thoughts on “AppInventor – Post Data to Google Sheet

  1. It’s very helpful. However, it’s only word with English words. When I submit with Chinese Characters. It shows “???” or sometime in blank. I try to use “PostTextWithEncoding” with no luck. Do you have any idea?

    Thanks in advance,
    Patrick

  2. So, I’m getting this working fine with 2 columns, but I’m missing something when I try three.
    I’m joining “name=” with “&time=” just fine, but when i try to add “&stamp” to it, the app fails to annotate the spreadsheet.
    I’m reluctant to post on the forums, as those guys aren’t very helpful, or downright rude .

    • Hi Brian

      A few things to check:
      Heading for “Stamp” in spreadsheet matches in (Google Apps Script ) GAS
      Syntax for new column in the GAS is correct: var stamp = e.parameter.stamp ;
      Added to the appendRow: sheet.appendRow([name,time,stamp]);
      You have your join block correct in the AI2 app
      Finally that you have republished the web app: Publish > Deploy as Web App > New Project version > Update

      Should work….

      • I don’t think the problem is the script. Here’s a block where I changed the column in the spreadsheet to “Action”, and republished the script.

        function addUser(e,sheet) {
        var time = e.parameter.time ;
        var name = e.parameter.name ;
        var action = e.parameter.action ;

        sheet.appendRow([time,name,action]);

        I think the problem is in the AI2 blocks.
        The app is still populating the first two columns, but not the third.

        ….and thanks for the quick reply…..

  3. YES!!!!! I had to Change the version number of the Script. Future revisions, I just deleted the script and built a new one. Thank you so much for this valuable tip.

    • Without seeing the contents of your GAS script it is difficult to give a full reply, but I would guess you either have a parameter missing from your settings in the script or on the sheet, not included in the appendRow, or you have not updated your script to reflect changes: republish the web app: Publish > Deploy as Web App > New Project version > Update

Leave a Reply

Your email address will not be published. Required fields are marked *