Google Sheets - Simple POST and GET Data

INTRO

Here is, in my view, one of the simplest approaches to uploading and downloading data to a google spreadsheet from AppInventor. It requires a google apps script web app, and will work with a private google sheet (as well as a more public one).

I am using a script project which is bound to the spreadsheet (see link at the bottom of the page for more details).

This uses code specifically for bound scripts only, and also code which selects the first sheet in the spreadsheet to post and get data.

I have also used getDisplayValues() to return the data, this returns what you see on the google sheet, and returns everything as strings, which is OK for App Inventor because App Inventor sees numbers strings as numbers, and for dates and times, allows you to use the clock blocks to manipulate a date or time (instead of having to manage a date number/ complicated date string from google sheets). You can change this in your script to getValues() if you want the original google sheet formats.

In this example I have used three textboxes for data in the postText list. You can have as many items in the list as you want, ensure you have them all in the correct order, the script will write them all out to cells in a row.

SETUP

  1. We will create a new spreadsheet, give it a name.

  2. Open up the script editor and paste in the script below. Save and then publish your script as a web app, with the web app executed as you (your google account, same owner as the sheet), and accessible by anyone (with 'even anonymous' if using the legacy script editor). Copy the script url generated for use in the AppInventor app.

  3. Download the aia project file (link at the bottom of this page) and open it up in your AppInventor. Paste the script url to the variable in the blocks editor.

  4. Open up the app in your companion / emulator and test.

SCRIPT

//This script is bound to the spreadsheet. It therefore uses getActive() to get the

//spreadsheet it is bound to, and getSheets()[0] to get the first sheet in the spreadsheet


var ss = SpreadsheetApp.getActive();

var sh = ss.getSheets()[0];


//POST function appends all data sent as parameters to the next row on the sheet, in the order sent


function doPost(e) {

var data = JSON.parse(e.postData.contents);

sh.appendRow(data);

return ContentService.createTextOutput(data);

}


//GET function returns sheet values as a stringified JSON

//Using getDisplayValues() returns all values as strings

//This is not an issue in AI2 as number=string=number

//Use getValues() if you want original value formats


function doGet(e) {

var data = JSON.stringify(sh.getDataRange().getDisplayValues());

return ContentService.createTextOutput(data);

}

BLOCKS

VIDEO

SimplePostAndGetData_GoogleSheets.webm