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.

34 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

  4. Hi Great instruction, but I am geting this from the app built in MIT app inventor ,

    Undefine, in the google spread sheet. when reaches the google doc spread sheet

    Could you directmy to a tutorial where:
    1. Data is collected by multiple devices
    2. Send from every single device to a computer or tablet by bluetooth, storred in a file.
    3. Then the file in the tablet is sent to google docs spread sheet.
    best Regards

    function doGet(e){
    var ss = SpreadsheetApp.openByUrl(” https://docs.google.com/spreadsheets/d/1RCgBkVyadgjV2eAeH-wWkqLQbmQxFBPM_H2NzSLznAo/edit#gid=0 “);
    var sheet = ss.getSheetByName(“Sheet1″);

    addUser (e,sheet)

    }

    function doPost(e){
    var ss = SpreadsheetApp.openByUrl(” https://docs.google.com/spreadsheets/d/1RCgBkVyadgjV2eAeH-wWkqLQbmQxFBPM_H2NzSLznAo/edit#gid=0 “);
    var sheet = ss.getSheetByName(“Sheet1”);

    addUser (e,sheet)

    }
    function addUser(e,sheet) {
    var id = e.parameter.id ;
    var name = e.parameter.name ;

    sheet.appendRow([id,name]);

    }

    • Would have to see your app blocks to understand why you are getting the undefined message ?

      For your other enquiry, it sounds like you need to use either tinywebdb, clouddb, or fusion tables, however if communicating locally with other devices there should be sufficient opportunity just using bluetooth to exchange data. You are best reviewing and searching the appInventor forum: https://groups.google.com/forum for possible solutions

      • Hi thanks
        I got it to work.
        ist it posible to have an GAS that would take a complete *.csv file. instead of ading rows, adds a complete file and append it?

        • Hi Metric: …say that function addUser chages grom adding parameters to adding a file, this extract comes from code to import csv files attached to an email…..

          var sheet = SpreadsheetApp.getActiveSheet();
          var csvData = Utilities.parseCsv(attachment.getDataAsString(), “,”);

          // Remember to clear the content of the sheet before importing new data
          sheet.clearContents().clearFormats();
          sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

          • Hi ,,, I am still trying. I got some help on AI2 forum for bulk uploads, to Google Fusion Table, how ever with FT hard to make data calculations .
            Another question how can I change my figure avatar ( the triangle)
            Best regard luisma

          • Keep asking on the forum, someone with more experience of Fusion Tables will help, I do not use them as happy with local – (tinydb) or cloud – (clouddb) (running my own redis server!)

            The avatars are automagically assigned by WordPress, commenters (even me at times) cannot change these. They are not the most flattering things 😉

  5. Hi Metric

    I got answer from the forum , how to send a file *.csv !!! And is pretty cool ,
    I will log from desk top to share the blocks by Abraham (ABG).
    But still on the learning path, since the current solution is for fusion tables.

    Thanks “Met”

  6. Hi

    When I post special characters, such as characters with tilde (é, ó, ú, etc), the spreadsheet ignores them. I’ve tried PostTextWithEncoding with encoding UTF-8, UTF-16 and some others, but none of them works. Does anybody have an idea about how to solve this?

    Thank you

  7. Hi

    I got my app to add data to my sheet but it stores “undefined” in the cells instead of the text i added in the textbox.

    What could possibly be wrong.

    Regards,
    Jason

    • Have a look at your blocks, and ensure the data is getting through to the url.

      You should be able to test your webapp from a browser:

      “https://script.google.com/macros/s/YOUR SCRIPT ID HERE/exec?id=10&name=Bob”

  8. Friend, excellent what you explain. From the cell phone it can be sent to the spreadsheet, Now, …
    How can it be achieved so that the textbox of the App Inventor application imports or receives a data from the spreadsheet?
    that is to say for example I have 4 columns in the calculation sheet
    No. Cellular Name Email
    1 Carlos 09866554 nnnngmail.com
    2 Andres 099554433 rrrr@gmail.com
    If on the cell phone I put the 2 in a textbox it should show the cell number 099554433 in a Label
    How can I achieve this ???
    Thanks in advance

Leave a Reply

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