Mirror Monday Board to Google Sheet

Questions & Answers
, ,
1

Good weekend Makers :cowboy_hat_face:

I have a scenario where I’m trying to mirror a Monday board in Google Sheet, that is, whenever there is a change on the Monday board, Make will update the relevant entries on the Google Sheet.

The Board/Sheet has rows of country names, lets assume unique, as the main column. The data is replicated from Monday to Google Sheets, so the starting point is that the Data is the same.

image
image832×294 31.8 KB

I currently have 3 Modules:

  1. Monday- watching Changes in the board and return the changes:
  • The Pulse/Name value, e.g. =Belgium,
  • The changed Column header, for example: Region
  • The new value, for example: Africa
  • Old Value: Europe

  1. Google Sheets - Search Rows: Im searching for the country Name in the Google Sheet, where the Country Name from Monday (Belgium) = The Country Name on the Google Sheet. The results return the Row Number among others

  2. Google Sheets: Update a Row: My next task is to update the relevant value in the Sheet, in our example, Update Column C (The Region Column) on the row the search returned, to “Africa”.

The update Row module requires you to define in which Column I would put the value, but it won’t always be Column C (Region) - How can I do it dynamically? :hot_pepper:

image
image669×534 38.8 KB

2

Hi @OmriNyx,

You could enumerate the column headers using a Get Range module, aggregate the column letter/number and header name, then find the column letter/number based on the name using a map() function, then pass the letter/number of the column on to the next module.

2 Likes
3

@Donald_Mitchell Thank you for the quick reply :grinning:

I’m not sure I completely understood your suggestion.

On the logic level, the only way I can think to populate the next module (Google Sheet: Update Raw) is if each time there is a change on Monday I will requests all the Fields of that specific item, then override all the item fields on the Google Sheet… This is probably the easiest way (I can think about) :melting_face:

Thank you nevertheless ! :blush:

4

Hello,

I must have misunderstood this question:

Why would Region not always be in column C?

If Region (or anything else) won’t consistently appear in Column C, you cannot use Update Row function unless you update the mappings in the module according to the column placement before the scenario runs.

If you want to update it dynamically, you’ll need to search your Header row for “Region” (and any other headers that won’t consistently appear in the same column), get the corresponding column number of that header, then update each individual cell until the entire row has been updated.

It would be best to avoid modifying the column placement, if possible.

If you use Update Row, you will need to map values for every column if there’s a chance any of those values will be updated.
As you suspect, the Google Sheet is mostly overwriting cells with the same values.
If you leave them blank, they don’t get updated or erased from the Google Sheet.

2 Likes
5

Hi @Donald_Mitchell sorry for the confusion, the column placement would always be in the same position, that is fine.

The issue is that sometimes one field of the of the column Region will change on Monday and sometimes info on the column Price will change and sometimes info on the column Stakeholder will change on a certain row… and so on…

As you rightfully wrote, The “update row” module requires input on in all columns, of course you can leave it empty, but how to dynamically input only certain columns on the module?

My solution is actually to override the entire row, no matter that maybe only 1-2 columns were changed, probably the easiest and most straight forward solution.

Still on the same topic, im mapping the “Get Item” Module from Make to the “Update Row” Google Sheet module, the operation returns a specific error on some of the columns as below.

image
image493×602 41 KB

How do I know what is Parameter 6 and 42?

Here is how to Google Sheet mapping looks like (partial screenshot):

image
image481×753 26.6 KB

In order to tachle that error I have tried using “variables” (light purple_ in some fields, whereas other I kept direct mapping from the Monday module. Most of the input fields are text.

Here is how the entire Scenario looks like:

image
image1763×394 74.1 KB

  • Once values update on Monday get the update immediately
  • Use the Item ID to get all the Items details
  • (Set some variables in case the result is not text)
  • Search Row on the Google Sheet to get the row number that is matching the country name
  • Update (override) the entire row data

Module 16 - Monday Get Item - the Output looks like this:

image
image475×879 32.2 KB

If I look at Column Value 6 for example, im mapping the Text Value to excel and even using a variable in case its not text, but still getting an error as mentioned above, but I dont know if this “6” refers to Parameter 6…
image

Any clue on the error handling?

6

Yea, that would really help with your data integrity.

I think I see what you mean now, the output from Monday needs to be accurately mapped over to the input on Google Sheet, but you aren’t sure if, for example, “person” will always be #6 in Monday’s output?

2 Likes
7

I think I see what you mean now, the output from Monday needs to be accurately mapped over to the input on Google Sheet, but you aren’t sure if, for example, “person” will always be #6 in Monday’s output?

So far that has not been my problem, I assume that could be a problem in case someone changes the Monday Columns order… So it may not be 6# but the Column Name ID will remain the same anyway, so im not worried about that too much.

I currently only have that bug mentioned above:

Is parameter 6 referring to the Monday 6# Output?

10

Just to wrap up the discussion. in the above mentioned error, the Parameter numbers refer to the Google sheet Columns, the first column A is 0, the second Column B is 1, C is 2 and so on…

The error itself was due to mapping an array instead of text.

3 Likes
11

Hello there @OmriNyx :wave:

I just want to quickly step in to say thank you very much for sharing the summary of the situation with the community :pray:

This is super valuable stuff and we 100% appreciate it :star:

1 Like