Google Sheets - Reading Columns And Rows

Published on March 14, 2022

In this guide, you will learn how to read a specific column/row from Google Sheets, and also how to get the number of columns or rows in a Google Sheet. You can also get the data of a specific cell.

This is a demo to let you know that you do NOT need any extensions to read data from Google Sheets. Simple, effective, and quick.

V2 new added features (03-14-2022):

  • Get the data of different sheets in the same spreadsheet, e.g. Sheet1, Sheet2.

  • Get the data of a specific cell from given co-ordinates and location.

  • Updated AIA file with all of the features.

🏷️ Tags: #tutorials-and-guides, #google

Step 1. Set Up Your Google Sheet.

  1. Open your spreadsheet.

  2. On the top right corner, click on Share.

3. You will see a popup. Change the permission so that anyone on the web can view (this is the minumim requirement, you can also make everyone suggest or edit).

4. Copy the sharing URL, not the one on the top of your browser.

5. Open Notepad, a new Google Document or anywhere you can edit text.

6. Paste the URL into the document. The URL should be something like this:

https://docs.google.com/spreadsheets/d/1FXGY3wic1HtZOxwqHHjzXqHzlAYTs1FGx9MbkFBYE40/edit?usp=sharing

7. Change that to something like this and copy the new URL you just made. Basically, remove edit?usp=sharing and change it to gviz/tq?tqx=out:csv&sheet=YOUR_SHEET_NAME. You can change Sheet1 at the end of the URL to the sheet that you want to get the data from. They can be found at the bottom of the spreadsheet. By default, they should be Sheet1. 1FXGY3wic1HtZOxwqHHjzXqHzlAYTs1FGx9MbkFBYE4 is the spreadsheet ID of my spreadsheet. All Google documents, slideshows and spreadsheets have their own and unique file ID, and you can ignore this by now.

https://docs.google.com/spreadsheets/d/1FXGY3wic1HtZOxwqHHjzXqHzlAYTs1FGx9MbkFBYE40/gviz/tq?tqx=out:csv&sheet=Sheet1

Step 2. App Inventor Blocks.

In your case, the csvUrl variable is the URL you just made. When the action has completed, for example, when the system has got the column number, it will set global output as the column number. Note that global output can either be a list or a number. Then, we show the output on a label.

To get data, you can call the procedures, like this.

👣 One Step Further

If you would like to only get a part of your spreadsheet as all of the activities, for example, only cells A1 to B2, you can add that at the end of your URL, for example, something like this (highlighted in blue).

https://docs.google.com/spreadsheets/d/1FXGY3wic1HtZOxwqHHjzXqHzlAYTs1FGx9MbkFBYE40/gviz/tq?tqx=out:csv&sheet=Sheet1&range=A1:B2