How to load data from a google sheet named table

How To
1

I am after some help loading data from a Google sheet named table “Table1” table (that I will use as look up values).

When using the Load rang from Google sheet it seems you have to specify an exact range. My data table can grow later to have more rows. How do I load the data from it without having to change the code each time I add another row?

Thank you.

2

Hello @zmk,

A Watch New Rows Trigger Module will run your scenario on any new rows it sees since the last time it ran.

If you’re just getting a range, you can specify a cell that’s way beyond the end of the range but the module should only return up to the last row in the range that has data, if that makes sense?
For example, if you only have data in A1:Z100, you can specify A1:Z5000 and it will only return up to row 100 since that’s where the data stops.

1 Like
3

Hi @Donald_Mitchell,

If I understand correctly then all I need to do is specify a large row number that will cover future growth. The module only returns rows that actually exists. This will achieve my goal.

Thank you.

4

Yes, give it a try, but I believe it should work like that.
Just be sure your range is always beyond what you would expect to have data in.

Also, I believe there is a way to get the “UsedRange” so you can always run that first to find out the last row and column with data then use that when you read a range. Let me know if you’re interested and I’ll find out how you can use it.

5

That would be great.

Thank you.

1 Like
6

I guess the best way is to just use Get Range and don’t specify an ending row.
It will get as much as possible and only return what actually has data.
Just specify which columns will have data, like this: “A:E”.

It will return one bundle for each row, even if a row has no data, until it reaches the last row with data in one of the columns you specified.

From the last row with data, where Bundle Order Position (__IMTINDEX__) matches Total number of bundles (__IMTLENGTH__), grab Row number (__ROW_NUMBER__) from that bundle, add 1, and that’s the next row that is ready for data.

3 Likes