Writing data back to Google Sheets based on Read Range

Help Studio
, , , ,
1

Total noob here but a very experienced programmer in other languages and trying to get a sense of best practices, etc. I’m looking to process a ~7,000 row Google sheet using the GSuit Activities tools by reading the range and using the data to update our core PeopleSoft system in a browser in batches. This all works great, however I was intending to mark each row in the Google Sheet as processed so next time I run the bot, it just picks up where I left off. I’m having trouble understanding how I’m supposed to write back to the same row just processed in my For Each Data Row in Data Table. Thanks in advance!

2

Hey @mnorris

Currently how are you writing it please.

Is it a range way or by row or by cell ?

Thanks
#nK

3

@Nithinkrishna I haven’t built the “write” portion of the workflow yet, I’m looking for some guidance on the best way to accomplish it, so you tell me I would be willing to do any of those.
M

4

Hey @mnorris

There are two best approaches I would say,

  1. Have the entire data table as global variable in main, for each transaction you update the status in the data table only & finally once all the transactions are done write it back to the Excel

  2. After processing every transaction, just update the transaction status of that particular row - just like how the Orchestrator queue works.

Hope this helps.

Thanks
#nK

5

Thanks @Nithinkrishna,
only other question I have is how would you handle this with a filtered dataset?
M

6

Sorry @mnorris What do you mean by filtered dataset here please ?

7

@Nithinkrishna ,
I “Read Range” into a Data Table, then used the “Filter Data Table” tool to remove any rows that have a “1” in Column C (this is the activity indicator I’m trying to write later in the process). Doing this provides me with a list of unprocessed rows which I feed into the “For Each Row in Data Table” to take action upon.
Thanks for sticking with me here, really appreciate it.
M

8

Hi @mnorris
Inside the for each row , update the status of each row into the excel file.

9

Hello @mnorris

In the iteration (for loop), you can use write range or write cell activity to update the value.

10

@Nitya1 and @Rahul_Unnikrishnan thanks for the input, totally with you on the concept, but still looking for a little clarity on the execution. Given that I have a filtered data table and thus only a subset of the full worksheet, what’s the best way to to reference the row ID in the Google Sheet? For instance, let’s say I only have a two row range, A:B, and A has already been processed and B hasn’t. When I apply the data set filter, I end up with a single row dataset in my For Each loop containing the data for row B. What’s the best way to get back to the reference to B so that I can Write Cell?
M

11

Yep got your query now.

Use the index of function which gives your row number,

DT.Rows.IndexOf(CurrentRow)

With this output you can frame the address of the G-Sheet Read Range Activity!

Hope this helps

Thanks
#nK

12

@Nithinkrishna thanks, we are super close now! Unfortunately this returns the row # in the filtered data table which doesn’t relate to the row in the Google Sheet. I tried passing CurrentRow into the original data table, but that returned “-1” or not found. Obviously I now see how I can make this work without the filtered data table, but that feature is super useful and I don’t want to just abandon it. Maybe I could lookup the key value from the current row back in the original data table to get the row ID?

13

Sorry little confused here.

So you have two data tables ?

14

Yes, that’s how I interpreted the Filter Data Table tool to work. See the attached screenshot of my workflow. I’m filtering out the rows that are already processed, processing the rows that are not, then writing the process indicator back to the newly processed rows.

image
image917×1989 52 KB

15

Okay clear, may be that’s why the error.

But is there any identifier column to use for finding the row number ?

16

Closing the loop here, I used a Data Table Lookup back to the original Data Table to get the row index and used that to write back to the Google Sheet. Thanks for all the help.

17

Perfect @mnorris

18

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.