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.

70 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…..

        • Well, have just tested by adding a third column to my original example (as posted) and it works fine. See OP for additional images

          • Hi, I have the same problem. I added several columns (including datepicker ,timepicker, and “ordinary” text columns). The code seems to be ok but the spreadsheet does not record any of the data provided by the smartphone. Any ideas?

            Thanks in advance

          • Work back through the tutorial, you will have missed something some where, probably you have not created a new version of the google apps script after editing – see other comments here on how to do this

  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 😉

          • Hi Metric
            FT is out how to write a webapp in Apscript to do file transfer insted of single lines.
            Have an app that collects data, stores it in csv file. and whwn one gets to a wifi or internet conection another app that send data to GSS. and makes a data base on it

            best
            luisma

          • I am working on something for transferring files to google drive (other than images) using javascript and base64 encoding. It may still be easier to send the data contained in the csv to a spreadsheet, as opposed to sending files. Easy enough to get the data out of csv files using AI2.

  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

  9. Hello, Could the script return message after post success or fail? I want to have a respond in the APP. Otherwise, I need to get the last few rows to compare the data?

    • Yes, you can use the contentService option in google apps script to produce an output that the web component can collect in responseContent

  10. So I see the first two columns in the appinventor part use id= and &name= what do I use for the third column to make it work?

    • If you have a third column titled “number” then you would expand your url block to include it: id=xxx&name=yyyy&number=zzzz. You can just keep adding 🙂

    • This method uses “append” so it will always add data to the next “last row”. SOunds like what you are looking for is full CRUD (Create Read Update Delete) on a sheet. I am working on such a solution. Coming soon…

  11. I need help for my project
    I need to read data from google spread sheet
    each column in the spread sheet will appear in textbox

  12. I work in a school and am helping a 6th form student with their wheelchair football app.

    I found this really useful, however I am getting this error message on both my work and the practice one on your site.- Error 1103: Unable to post or put the text “id=&name” with the specified URL: (url from the publish bit on the Google Drive App Script)
    Have you any ideas of how I can sort this out please.

    Thanks

    Alison Lynch

    • Just guessing, without seeing your blocks or code, but it seems you are not getting anything through from your textboxes. There should be a value after id= and after name= ?

  13. I do like here, i want to send coordonates instead of text but it didn t work … i changed id and name with latitude and longitude but i can t send them. Any ideas ? ..

    • Please return to the forum and post your blocks and your google apps script. Check your script has been published to a new version.

  14. Hi,
    Thank you very much for those explanations 🙂
    My script works well and I can modify my Google spreadsheet when I test it
    However, when I test the app on my phone, the app opens but the spreadsheet does not receive any data
    I have changed the confidentiality options in my GoogleSheet so that everyone with the link can access and modify it, I have set my script to public as well and change the parameters of my phone so that an unknown app can be installed… but still nothing works…
    Any idea why ?

    • Without having access to everything it is difficult to know where the problem lies. If you visit the AI2 forum there is a current thread running on this. You need both the get and the post functions in your web app, google needs the get for using a browser and post for Ai2.Make certain you have published your app to a new version and have given permission for it to run as you.

  15. hi, awesome thread.

    how can i write the data in specific columns, not just id and name in column 1 and 2?

    but access the 5th column which is called car, lets say.

    • You will see in my post how to add additional columns. If you only want to add data to a specific column, just leave the textboxes blank you do not want to fill (or better put in a 0 or * or other marker). You must still supply the parameters, e.g. id= , name=, even if you leave the textboxes blank/put a marker, otherwise you get an “undefined” in the google sheet cells not listed

  16. Hi. I’m trying to add about 14 columns but it doesn’t work…

    Heres the code:

    function doGet(e){

    var ss = SpreadsheetApp.openByUrl(“https://docs.google.com/spreadsheets/d/1h3LW2Jo-lENpVk6TIJAxGTim_Ce9Y5AyGRf8jnAHylI/edit#gid=0”);
    var sheet = ss.getSheetByName(“Sheet1”);

    addUser(e,sheet);

    }

    function doPost(e){

    var ss = SpreadsheetApp.openByUrl(“https://docs.google.com/spreadsheets/d/1h3LW2Jo-lENpVk6TIJAxGTim_Ce9Y5AyGRf8jnAHylI/edit#gid=0”);
    var sheet = ss.getSheetByName(“Sheet1”);

    addUser(e,sheet);

    }

    function addUser(e,sheet) {

    var lastname = e.parameter.lastname;
    var firstname = e.parameter.firstname;
    var title = e.parameter.title;
    var gender = e.parameter.gender;
    var dob = e.parameter.dob;
    var skills = e.parameter.skills;
    var experience = e.parameter.experience;
    var languages = e.parameter.languages;
    var phonenumber = e.parameter.phonenumber;
    var address = e.parameter.address;
    var latitude = e.parameter.latitude;
    var longitude = e.parameter.longitude;
    var selftyped = e.parameter.selftyped;
    var city = e.parameter.city;

    sheet.appendRow([lastname,firstname,title,gender,dob,skills,experience,languages,phonenumber,address,latitude,longitude,selftyped,city]);
    }

    • Have you republished your web app to a new version after any changes ? (Your web app looks OK)
      Check your block syntax for all the required “&=” and so on ?

      For more help, join the AppInventor Forum and post your blocks

  17. Hi,
    I am getting this error message.- Error 1103: Unable to post or put the text
    “id= 1&name= prueba” with the specified URL: (url from the publish bit on the Google Drive App Script)
    Have you any idea what happend?

Leave a Reply

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