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

[TIMAI2]

With reference to this previous guide:

Google Sheets For AppInventor (replicant!)

Based on the work of Tommy Heng, a google apps script version of the proposed built-in MIT Google Sheets component. Can be considered as a primer on how to use google apps script to do all (?) the things people want to do with google sheets....Includes a barebones aia project (with script file) for developers. [GSFA2] [GSFA1] [image] Google Sheets for AppInventor

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:

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 , 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.
#####################################################################

[SHUBHAMR69]

@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.

[TIMAI2]

Use the GetFunctionOutput event to get the returned data

[Leni]

what means script url?

[gordonlu310]

@Leni

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.

[Faraz_Firoz]

You can take help from here How to use google sheet

[TIMAI2]

@Leni

what means script url

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

[Education_Plus]

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

[TIMAI2]

@Education_Plus

can you please share source code

Now available in the first post.

[Bryce_Sconyers]

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.

[TIMAI2]

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.

[Education_Plus]

Than you so much.

[Spicy_Topics]

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]]

[TIMAI2]

@Spicy_Topics

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

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

[Spicy_Topics]

Got the mistake.. sorry..

@TIMAI2

ID parameter to the script

yes

[TIMAI2]

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

@Spicy_Topics

/exec?&SH=

[TIMAI2]

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

[Salman_Dev]

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

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

[TIMAI2]

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.