Know how many cells with content are in a specific column of a Spreadsheet

MIT App Inventor Help
1

I need to know how many cells with content are in a column of a Spreadsheet and save this number of cells in a variable.

2

what kind of content?

3

I mean that I need to know how many cells are not empty in a specific column

4

Is there a different column in your spreadsheet with contiguous data (for example an id number, or other column where every row is filled) ?

5

Well, I need to know how many cells are not empty in the column C, E and D of the same Spreadsheet, for your information, in those columns there are a word for cell, these are three lists of words

6

That is not what I asked...

7

I'm sorry, I didn't understand you. I tried to add more information to understand what I'm trying to do.

8

Is you data something like this:

image

Where columns A and B (could be just one of these) have all rows entered.

9

No, it's like this:

image
image973×983 89.8 KB

I only need the large of the column C, D, and E. The end of these columns is like that:
image
image543×1087 38 KB

10

OK. If we count column A, then we will have the total number of rows ?

11

Well, there are exactly 45922 rows in this Spreadsheet

12

Good. If we count column A, then we will have the total number of rows ?

13

Yes, I swear

14

Thank you. The reason for all the questions is that I wanted to see if running a gviz query would work in your case. Looks like it will. I will work up a formula for you, based upon my simple (and short example).

15

I want to add that I tried to do a Script in the Spreadsheet but it produces lots of errors and the App Inventor don't know how to put the number in a variable, when I use the link that gives me the Script it seems like that:

image
image1568×187 21.3 KB

16

Here you go:

  1. Assumes your spreadsheet is set to "Anyone with the link"
  2. You use a web component in your app to return the data as a csv
  3. I included column A for good measure...
  4. You will find your SHEET ID and GID in the url address bar for the spreadshet

DATA in SHEET

image

BLOCKS

image
image701×415 44.1 KB

QUERY URL as text:

https://docs.google.com/spreadsheets/d/
<SHEET ID>
/gviz/tq?tqx=out:csv&gid=<GID>&tq=
SELECT count(A),count(C),count(D),count(E) label count(A) 'No', count(C) 'Barcode', count(D) 'Product', count(E) 'ProcDate'

OUTPUT

as html
image

as csv

"No","Barcode","Product","ProcDate"
"5","2","3","4"
17

Well, It only needs to return one of the numbers, for example the C column number, without the title, only the number

18

Ah, getting picky now :wink:

I will leave you to figure out how to edit the url to return the value for just one column (should be obvious), and how to handle the returned csv to extract the number (the output always returns a header for count).

An alternative, simpler method would be to setup some formulas in the spreadsheet, then return these with a web GET:

=counta(C2:C)

https://docs.google.com/spreadsheets/d/
<SHEET ID>
/export?format=csv&range=H1:L1

(where the range contains a formula for each column)
1 Like