Get range from Google sheet to populate spinner elements

Hello Everyone,

I have an issue where my spinner elements have to be added every few days, so is there any chance to get the spinner elements from a range in google spreadsheet (all the valid values in range - so that i will add new in the same range).

Thanks in advance.

Yes

You can use this:

or

or this may give ideas

Also:

and

Is it possible to retrieve the last row in a sheet without knowing how many rows are in the sheet when you make the request? Something like range ( A?..C )? I can get the last row data by downloading the sheet as a csv; converting it to a List, then selecting only the last item in the List. :slight_smile:

Just the last row ?

yes , without knowing how many rows are currently in the spreadsheet. Can't seem to do it with sql requests, only using the csv. I have a date in the C cell so I can retrieve max(C) but that is not the entire row. Select A, B, max(C) group by A,B,C or select A,B,C where C contains 'max(C)' do not work.

You should be able to use "offset" and "count" in your query to return the last item, I would have to go away and have a play to come back with the correct syntax....

Using just gviz, it is not possible, unless you have a descending index on your records (or add one to your google sheet using an arrayFormula).

If you have an index then you could use a query like this:

SELECT * WHERE F matches 'MIT' order by A desc limit 1

or simply

SELECT * order by A desc limit 1

You can use nested queries or functions inside your query directly on google sheets to return the last column. This method would allow for this:

ArrayFormula to add an index - you put this in your headers row (1)

={"Row";arrayformula(if(A2:A<>"",row(A2:A)-1,""))}
1 Like

An alternative is to just call back all the data on the spreadsheet as a csv into the app and then select the last item in the list.

That is what I have been doing. Using a csv should work for Raghavender too to set his spinner elements. Thank you.:slight_smile: I'll look at the sheet solutions too. I probably can set up an index by adding a column.

A slightly more long winded route is to make two gviz calls to the sheet.

The first query to return the number of rows, then the second query can use this to return the data in the last row

First query:

SELECT COUNT(A)  ## assumes there is something in every row in A...

will return a count of all the rows, including the header row, you can do this with a WHERE clause as well to return the number of rows that meet a condition

Second query: (let us assume the output of the first query = 100)

SELECT * OFFSET 99  ## deduct 1 to start querying at 99th item

Again you can use the WHERE clause to define conditions

Unfortunately, the gviz syntax in the select statement does not appear to allow nesting of select statements - I could find not evidence of others doing so (even some of the mighty on StackOverflow...)

@TIMAI2 thank you.

This solution to use a GoogleSheets Range worked for me. The function updates the contents of row 1 E,F,G to the contents of the most recent PUT .A Range of E1:G1 always contains the last row data. E1..G1 always contains the most recent input to the sheet. :slight_smile: (The extension I populate the sheet with does not have a way to automatically index row :cry: )

Just for completeness I have worked up an automatic indexing arrayformula that does not fill down to the bottom of the sheet. Relies on contiguous data in column B (no blank cells between data)

={"Index";arrayformula(if(indirect("B2:B"&COUNTA(B2:B)+1)<>"",row(indirect("B2:B"&COUNTA(B2:B)+1))-1,""))}