How does gsheet's "update a row" module work?

How To
1

CONTEXT
I’m building a tool to keep my contact lists in sync across 3 places. google sheets is my central ssot, and the other two (ios contacts and gcontacts) are the main sources of original inputs.

those inputs correspond to two scenarios I’m currently solving for: adding a new contact or updating an existing contact.

PERCEIVED PROBLEM
in building the components for updating existing contacts, I’m running into one of those things where you say to yourself “this can’t be how this is supposed to operate, is it?” this is that thing:

it seems that the the “update row” module needs to account for a wide range of potential update types in each relevant cell:

if the new bundle contains data for this cell, and

  • the cell is empty, do X if we know A; otherwise add the data
  • the cell contains the same data, ignore
  • the cell contains the same data along with other data not found in the new bundle, do Y if we know B; or Z if we know C; if neither are verified, do D.
  • the cell contains data, but none of it matches the new data, determine E, F, or G, and do 1, 2, or 3 for each of those respectively
  • and whatever you compare with the cell’s content needs to be formatted like-for-like
  • and if you update the cell, the data needs to be formatted like-for-like and appended in particular ways
  • wrap it all in toString( and trim(

writing out a formula for each property (and for all future props) seems like quite a commitment, to say nothing of the added complexity new sources, changing formats, or new categories of info would bring. (gah! and the maintenance!)

QUESTION
let’s just say I’d hate to commit only to find out that there’s a much simpler way of doing this. what say you: am I approaching this the right way? am I misunderstanding something about the module?

POTENTIAL SOLUTION
my gut tells me I’m looking for a type of meta formula that applies to each matching field. something like:

  • in the search module, find a uuid that matches the uuid in this bundle
  • where there is a match, determine which columns contain differences [ofc insert formula for formatting the new bundle’s data to compare like-for-like]
  • pass the row # and the column #s to the update module via the filter
  • for A types of differences, do X; for B, do Y; C → Z.
2

Hey Mike,

you can use a router module followed by filters and split the formula in separate much smaller and easier to manage pathways. Say if you have 10 cells you need to check → have 10 paths after the router, one for each cell. This way it will be much easier to manage and write out. On the down side, it will consume more operations then having everything happen together at the same time.

1 Like
3

that’s an interesting idea @Stoyan_Vatov!

let me work this out:

I’m imagining something like this: [1] watch for changes → [2] search for row matches → [3] filter for positive matches → [4+] filters by cell → [X+] update cell(s)

as you alluded to, the number of bundles – which determines the number of operations – would match the number of fields changed. meaning more operations.

but building on your idea, I could create a filter for each change type, whereby cells were grouped by functions under a particular generalized formula. that would have some nice benefits:

  1. minimal maintenance/troubleshooting efforts (as you mentioned)
  2. reduce the bundle/operation count
  3. because it would be more efficient, I’d have more freedom to change the structure in the future

splendid. thanks for the great suggestion. I’ll test it out and report any noteworthy results for the sake of community records. I’ll also leave it as unsolved for a bit to gather more thoughts.