Hi, all, I'm trying to build a Timer Server Action that connects to a google sheet, retrieve data and to perform a entity update based on the values in the google sheet
I'm using the Google Sheet Integration Service forge application, and is able to retrieve the data using GetValueByRange function.
My challenge now is in parsing/rebuilding the values returned from the Response of GetValueByRange, as the Row/Column data returned here is not gonna be directly applicable in how I want to use it.
For example, each cell value is actually a 'ValueItem' while each Row is actually a 'Values'
For example, if the sheet have 3 columns, first column being the ID, 2nd Column and 3rd column being the cell values, I'll need to loop through ValueItem to get the values stored here Values.Current.ValueItem.Current
I want to rebuild the data retrieved from the loop into something like this, which makes it easier for me to process in the next step, as I'll be able to pass it to the UpdateEntity function.
Assuming the first column is always the ID field, and I'm trying to build a structure that can cater for multiple columns that is dynamic in nature, let's say up to X Attributes
I'm currently looping through the Values.Current.ValueItem and using a counter to keep track of which 'column' of data I'm currently at, for example
if LoopColumnCount = 0, this is the value cell of the first column, which I'll assign to the ID attribute of my SheetStructure.
if LoopColumnCount = 1, this is the value cell of the 2nd column, which I'll assign to the Attribute2 of my SheetStructure, and so on
I'm currently doing it using switch, while it works it makes a big mess.
Can I do something like this in my assignment instead? Replacing the 6 with the Counter variable instead? In this way, I'll have a much cleaner code, instead of hardcoding each AttributeX with a fixed Switch condition and assignment
Or maybe I'm totally off in my solution, and there's a much better way to do this instead entirely
Thank you
This is how it looks like now, after getting the data from the ConnectToGoogleAndSpreadsheet service, I need to do 2 loops, 1st is to loop through each row, and the 2nd is to loop through each column and get the values that I need to perform the data update
Hi Wai,
You could make your code slightly less messy by using a layout like this:
Also, it may be a good idea to abstract the switch in an action, so your main actions looks cleaner.
As for your other question, you can't index a structure, a structure is not a JavaScript object. You can index a List though, so if you are certain there's always a given number of columns, you can build a list without data (using ListAppends), then index that list.
Another alternative could be creating a JSON representation of the structure from the data that comes from the sheet (or maybe there's already some Sheets API that can do this?), and use JSONDeserialize to convert it to the structure.