🟩 [FREE] Google Sheets for AppInventor (replicant) using Google Apps Script

With reference to this previous guide:

I have now written an extension to compliment the google apps script, therefore no need to write any complicated block structures. The extension follows the original blocks provided by Tommy Heng, but updates and improves on them. It has not yet been aligned with the latest version of the Google Sheets component currently available for testing (as of writing this), although it appears to be much the same.

Google Sheets for AppInventor

  • Latest Version: 1.0
  • Tested: This has been tested on App Inventor 2 using Android 10 and 12 devices, companion 2.62, and compiled apk. It may work on other derivatives....e.g. Kodular/Niotron/etc.
  • Released: 26 March 2022
  • Last Updated: 26 March 2022
  • Built: Using the Rush Extension Builder by @shreyash
BLOCKS & USAGE:

GSAI2DesignerProperties

Extension (you will need to setup the google apps script for this to work, therefore follow the links to ensure you have everything you need):

Google Sheets for AppInventor V1.0

Script File

Source Code for extension
GSAI2.java.txt (11.0 KB)

Bugs

A bug in the "InsertFormula" block has been found by Adhuham Rameez. The plus sign, '+', in any formula requires encoding. A quick fix is to use the UriEncode block from the web component before the formula

Enjoy :smiley: , and all feedback and comments welcome.

Available for use in any MIT AppInventor projects or competitions

#####################################################################
This work by TIMAI2 is licensed under a
Creative Commons Attribution-ShareAlike 4.0 Unported License with attribution.
Please use name = TIMAI2 and link to this source page when giving credit.
#####################################################################

16 Likes

@TIMAI2

There is a method to read a cell but how could I get the value inside the cell ?

I tagged you cuz I need to know about how to get the contents of a cell, ASAP as I don't have much of my time-period left to do things.

image

Use the GetFunctionOutput event to get the returned data
image

what means script url?

You have to deploy your own Google Apps Script project and use the deployment URL as the ScriptUrl. Search the forum for how to deploy a Google Apps Script.

You can take help from here How to use google sheet

All the guidance is available if you follow the link in the first post

hello can you please share source code of your extension we want to upgrade it and give contribution in it.

1 Like

Now available in the first post.

2 Likes

I noticed a small bug in the original google script. In the code that handles readCol, sh.getRange should start at 1 and not 2, otherwise the first row is never read when reading the whole column.

1 Like

Thanks Bryce. This could have been by design at the time, I cannot remember, and now seem unable to test the actual component, in order to check whether it is/was returning the column without the header row. Regardless, I will update the script.

1 Like

Than you so much.

Hello @TIMAI2 .. I have been using your script only in my apps and works good. But suddenly now it is showing this error and app doesnot send values to ghseet

https://script.google.com/macros/s/AKfycbys4dSSJAQn6akQ0vFrW0H622TTnyCsNc1EKz4O5Oc_uqoZJ90/exec?&SH=SCIENCE&FN=writeRange&REF=D2:D44&DATA=[[%2236%22],%20[%2236%22],%20[%222%22],%20[%2225%22],%20[%2217%22],%20[%2218%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%22%22],%20[%2250%22]]


Can't see you sending an ID parameter to the script ?

Got the mistake.. sorry..

yes

1 Like

Your complete script url should be including the ID ....

https://script.google.com/macros/s/AKfycbys4dSSJAQn6akQ0vFrW0H622TTnyCsNc1EKz4O5Oc_uqoZJ90/exec?ID=1uL7gYt4t4Jc6DKh7t_4hxqh0gV3mzVyPkfnnm9Z_tJo&SH=SCIENCE&FN=writeRange&REF=D2:D44&DATA=

Also note this in your current script url

1 Like

Don't worry, I spent a couple of hours trying to fix a problem with a partially transparent png, until I finally realised it wasn't transparent at all :wink:

2 Likes

5 posts were split to a new topic: I am getting data, i think in json. i am trying to separate it by column

Hi TIMAI2 please guide me, I have a problem using your extension.

How can I get the output of this extension as an AI2 List?
I'm using the "readrow" block and I don't know how to return the output as an AI2 list :pleading_face:

I have used a lot of methods but they didn't work

The output is returned as a stringified json array. You need to use the web component's JSONTextDecode block to convert to an AI2 list.