🟩 [FREE] GS Connected - A Swiss Army Knife for Google Sheets

[TIMAI2]

This is an offshoot from my GDConnector extension, and could be considered to be the big brother of my Google Sheets for AppInventor extension.

With this extension, the blocks and the google apps script will only use the google drive and google sheets stored on the developers google drive - the one who sets up the apps script. This removes the need for any authentication, but does have privacy issues for users.

You will be able to perform some basic drive functions to create folders and sheets, and then a wide range of sheets functions to allow for full CRUD functionality (Create,Read,Update,Delete), and much more, on google sheets. By using a google apps script, you can work with private or public spreadsheets.

• Latest Version: 2.1
• Tested: This has been tested on App Inventor 2 using Android 10 and 13 devices, companion 2.65, and compiled apk. It may work on other derivatives....e.g. Kodular/Niotron/etc.
• Released: 28 March 2022
• Last Updated: 29 January 2023
• Built: Using the Rush Extension Builder by @Shreyash
• V2.1 Update: added two variants of the AppendRow block

BLOCKS & USAGE

V2.1 Added Blocks

When working with ranges, rows and columns, please pay particular attention to the list structure format requested for each type. This is shown in the blocks (selectionIndex 18 - for example). Ensure you have ShowListsAsJson ticked in your Screen1 Designer properties. In general, the data returned from the script is a stringified JSON, you can use the parse block in the extension to convert this to an AI2 list.

SCRIPT & SETUP

Script GSConnected.txt

Once you have created your new apps script, delete anything in Code.gs and paste everything in the above txt file. Save, then publish as a web app, with "Me - your google account" and access to Anyone/Anyone even anonymous. Get the script url for use in your app.

How to create a google apps script web app - you can use a bound or a standalone script for this.

• Correctly Deploy a Google Apps Script Web App for AI2
• New Versions for New Google Script Editor

EXTENSION

The extension will only work once the google apps script is in place
GSConnected

Enjoy , and all feedback, comments and ideas for further development 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.
#####################################################################

[Faraz_Firoz]

@TIMAI2 where we can get sheet id

[Gordon_Lu]

I think if your sheet URL is

https://docs.google.com/spreadsheets/d/ABCDEFG/edit#gid=12345678

then your sheet ID is 12345678. The sheet ID for Sheet1 is 0.

[Gordon_Lu]

Yes, the sheet ID should be text/number/both.

[Faraz_Firoz]

I am using sheet1 so Am I need to enter 0 in
SheetId

[oseamiya]

I think you will find sheet id in the url and sheet name at the bottom.

[dora_paz]

[Gordon_Lu]

A page or tab within a spreadsheet. A Sheet resource represents each sheet and has a unique title and numeric sheetId value. You can find the sheet ID in a Google Sheets URL:

https://docs.google.com/spreadsheets/d/aBC-123_xYz/edit#gid=sheetId

Google Sheets API Overview  |  Google Developers

Sheet ID is an identifier of a Sheet in a Google Spreadsheet file. To obtain it, open the file for the desired Sheet, and locate it at the end of the URL after 'gid=' : https://docs.google.com/spreadsheets/d/1EwXZweUNKKGSC6k6_6IkukyRFeNzi7qEIIjOAC9_vGA

Where can I find the S... | Meiro Docs.

https://docs.google.com/spreadsheets/d/FILE_ID_HERE/edit#gid=SHEET_ID_HERE

[TIMAI2]

There is some confusing stuff here, especially from Gordon

I have updated the extension to rename SheetId to SpreadsheetId:

Here is a google sheet url:

https://docs.google.com/spreadsheets/d/1IdpGILd7Nii8nq5yb2Uq3kVFyyL50eo5la2xbDN1Cok/edit#gid=0

The spreadsheet file ID = 1IdpGILd7Nii8nq5yb2Uq3kVFyyL50eo5la2xbDN1Cok

The gid (gridId) = 0

This is the default gid for the first sheet in a spreadsheet when the spreadsheet is created.
Additional sheets (grids) will have a longer number for their gid, e.g. 331107384

The sheetName = Sheet1

for the first sheet in a spreadsheet when the spreadsheet is created. The user can rename it as they wish. This is shown on the spreadsheet along the bottom, as indicated by @oseamiya and @dora_paz above, and here below

The extension requires a SpreadsheetId and a SheetName. These can be set in the Designer, or with blocks at runtime.

The gid is not used in the extension.

[Gordon_Lu]

Thank you, now everything is cleared!

[Faraz_Firoz]

I am getting html code in listview when I am using this extension

[dora_paz]

Maybe post a screenshot, works fine for me

[TIMAI2]

@Faraz_Firoz

I am getting html code in listview

This indicates an error with the url being sent, or the script is not setup correctly.
The error message is usually at the end of the html.

[Faraz_Firoz]

Can you please tell me what are the things we need to edit in ScriptGsConnected.txt

[Gordon_Lu]

Just copy ScriptGSConnected.txt, delete everything in the script editor and paste.

@TIMAI2

Once you have created your new apps script, delete anything in Code.gs and paste everything in the above txt file.

[Faraz_Firoz]

My steps are

But I am getting error

[SHUBHAMR69]

How can I access the data in a Cell of the Spreadsheet ?

[TIMAI2]

@SHUBHAMR69

How can I access the data in a Cell of the Spreadsheet

and just use a single cell reference, e.g. A4

[SHUBHAMR69]

But how can I assign the value (text) to a variable ?

It is a void method according to the image right ?

Edit : OK I just saw the GetFunctionOutput()

But could you make this as a value returning block in the next update ?
Cuz it becomes quite complicated to get the variables to the exact values. (I know a way though, but still it becomes quite harder.)

[Gordon_Lu]

You have an event called GetFunctionOutput. Use this concept and translate this into blocks.

if (event = GetRangeData) {
set variable to output;
}