Google Sheets for AppInventor

UPDATE

I have now written an extension to work with the google apps script, no more complicated blocks!

See HERE for more details

INTRO

Just for fun, I thought I would have a go at replicating the components created by Tommy Heng for the upcoming Google Sheets component in AppInventor. I have used a google apps script web app to handle all the functions and created a demo app to show most of the functionality. I have also added in a couple of things that were commented on in the topic on the forum. I will provide a "barebones" app project as well that simply handles the connection with the web app - just the back end of things, so developers can build their own thing. Perhaps the only main difference is that I have not handled situations where, in the proposed component, blocks are combined to get the A1 notation reference and then run another function with them. This can be done, but would need to be in an asynchronous way - do the first, then once done, do the second.

WEB APP SCRIPT

Notes:

• No need to find the gridId or gid for a sheet, all functions use the sheetName, defaulted to 'Sheet1'

• Will work with spreadsheets that are private

• Function available to add a formula to a sheet

• Query function will handle most, if not all, requirements for VLookUp, HLookUp and Filters

• Query function can be set to return or leave out the headers

• "readSheet" will return the headers and all the data

• No requirements for setting up service accounts, the developers web app is all that is needed

• All links to script, spreadsheet, etc. are set in the blocks, these can be obfuscated or encoded/encrypted

• Can be "developed on" to access a user's own (and private) Google Sheets on their own Google Drive (See Google Drive Connector)

• Download sheet to csv file functionality can be added using the "readSheet" function and File component

• Developer can use correctly formatted List blocks to write data to spreadsheet (row/column/range)

• Web App will work with a standalone or bound script - uses spreadsheetIDs and Sheetnames from app

• Will work with any number of spreadsheets, or a single spreadsheet with any number of sheets(grids) created by the developer

• In the demo, all responses are returned to a label in a scrolling arrangement

BLOCKS

AIAs

GoogeSheetsForAppInventorDemo.aia

GoogleSheetsForAppInventorBarebones.aia (includes text file of web app script)

RESOURCES

HOWTO: Create a Google Apps Script Web App bound to a Spreadsheet

Link to Spreadsheet  (note: this is editable for people with a google account, and the data is reset every 24 hours - 3-4 am GMT)

Note:

• The web apps script refused to parse to json an element that contained a '&'. These cannot be escaped in json.parse so I do not know why this doesn't work. Check your data, and any data you intend to write to the spreadsheet for this character, and replace with 'and'. If I find a solution I will make a change.