Updating specific fields in google sheets

[Ecco]

I want to be able to bulk update ONLY cells in the A column from ‘FALSE’ and turn them into ‘TRUE’. I can’t seem to find the correct way to do this. Update A Cell/Update A Row/Bulk Update Rows (Advanced) modules all ask me to specify which cell/rows I want to update but I want it to automatically detect and update specific cells which contain certain text (in this case, the word FALSE).

[mszymkowiak]

Hi,

Yes, it is possible to achieve this, but you will need to build a custom API request. Here are the steps to accomplish this:

1. Perform a Search : Use a search module to find all rows in your table where column A contains the word “FALSE.” You can use the simple search module to simplify the process. If you need to use an advanced search, ensure it also returns the row number (you must include it as a column value in the spreadsheet).
   

   

2. Aggregate the Results: Once you have the row numbers which you want to update, aggregate these results into a JSON format. This will allow you to prepare a batch update payload.

You can auto-generate data structure using this sample JSON:

{
        "updateCells": {
            "range": {
                "sheetId": 0,
                "startRowIndex": 22,
                "endRowIndex": 23,
                "startColumnIndex": 0,
                "endColumnIndex": 1
            },
            "rows": [
                {
                    "values": [
                        {
                            "userEnteredValue": {"stringValue": "A23 new value"}
                        }
                    ]
                }
            ],
            "fields": "userEnteredValue"
        }
    }

If you want to send TRUE/ FALSE as Formula- change userEnteredValue to formulaValue

{
    "userEnteredValue": {"formulaValue": "=SUM(A1:A10)"}
}

4. Configure Aggregate to JSON module:

• sheetId- your sheet ID, “gid” parameter in URL
  

  

• startRowIndex- row number minus 1

• endRowIndex- row number
  

• startColumnIndex- for A it is 0, for B it is 1 etc.

• endColumnIndex- for A it is 1, for B it is 2 etc.

• stringValue or formulaValue- in your case TRUE or =TRUE

• fields- “userEnteredValue”
  

6. Make the API Call: Use Google Sheets “Make an API Call” module.
   
   

If you need more detailed guidance on constructing the API request or any other part of the process, feel free to ask!

Best regards,
Michał from Simplymation

[samliew]

@mszymkowiak is there a reason that you are not using the “Bulk Update Rows (advanced)” module for this, since it is available?

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

[mszymkowiak]

is there a reason that you are not using the “Bulk Update Rows (advanced)” module for this, since it is available?

@samliew Yes- but it is possible that in the same time answer should be “no”

I chose to use an API call because, in the OP’s case, the rows are not in a specific order. For example, FALSE can appear in rows 3, 6, and 9. Using an “API call” allows me to easily modify only these specific rows.

It might be possible to achieve this with the native module, but based on the bundle content, I had some doubts. Due to time constraints, I opted for the API call.

What do you think?

[Ecco]

can you elaborate on how you would use the bulk update rows advanced module to achieve this? @mszymkowiak’s example is doable but I want to see if using your method makes it a bit more simple.

[Ecco]

@mszymkowiak Can you please guide me on constructing the API request. I’ve just made one and got a 200 status code but when I checked my google sheet I noticed a problem.

I used the stringValue and it updated only A2 with ‘TRUE’. Whenever I use the formulaValue it updates only A2 with ‘ERROR’.

{
        "updateCells": {
            "range": {
                "sheetId": {{54.sheetId}},
                "startRowIndex": 0,
                "endRowIndex": 6,
                "startColumnIndex": 0,
                "endColumnIndex": 1
            },
            "rows": [
                {
                    "values": [
                        {
                            "userEnteredValue": {"stringValue": "TRUE"}
                        }
                    ]
                }
            ],
            "fields": "userEnteredValue"
        }
    }

[mszymkowiak]

Change cell value type to text in Google Sheets, it should help.