Updating specific fields in google sheets (cont.)

How To
, , ,
1

I’m having trouble bulk updating non-adjacent fields from a single column with the value ‘FALSE’.

I manually enter data into the google spreadsheet. I’m using a google sheets search module to get the row numbers for cells with the value ‘FALSE’. My goal is to have the scenario automatically update each and every non-adjacent cell that has the value ‘FALSE’ and turn it into ‘TRUE’. Previously, I’ve been told that this can be achieved by using the google sheets bulk update rows (advanced) module and the google sheets make an api call module. I’ve tried both these modules already but can’t seem to get the results that I want.

ps. Maybe I’m not configuring the google sheets make an api call module correctly? I’ve used both the stringValue and formulaValue as the userEnteredValue but the scenario only updates one cell (A2).

reference to post made two days ago which has not been resolved

image
image1364×225 19.4 KB

image
image431×911 35.6 KB

image
image
image1338×220 18 KB

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

Hello @Ecco,

Please edit or append your post to include the full context of the new question.
You can leave the reference to the old post there for context/reference, but it would be very helpful to state a new question.

Just to confirm…

Do you still have the same goal of updating non-adjacent cells in a single column in a single API call or minimum number of API calls?

You can’t use Bulk Update Rows (advanced) because the cells you need to update are non-adjacent?

You prefer not to use Update a Cell because it will use too many ops?

3

thanks for the reply @Donald_Mitchell. I will make the edit it right now to make sure it includes more information.

Replies to your questions:

Do you still have the same goal of updating non-adjacent cells in a single column in a single API call or minimum number of API calls?

Yes

You can’t use Bulk Update Rows (advanced) because the cells you need to update are non-adjacent?

I was told that I can by just inserting a column where I input row numbers manually. I’m not sure how that works though since I’ve never tried it.

You prefer not to use Update a Cell because it will use too many ops?

It does not matter to me how many operations it takes, as long as it updates non-adjacent fields in a single column. New entries will be added periodically, so the search rows module must be able to identify which cells to update and which cells not to update based on a certain value (in this case ‘FALSE’).

4

You already know what column needs to be updated, so as long as you know the row number that needs to be updated, you can use Update a Cell module to update one cell at a time.
If you have a list of row numbers you can iterate on that.
It will use up one Op per cell that needs to be updated, but it will at least get you started.

5

It will use up one Op per cell that needs to be updated, but it will at least get you started.

you are right. then maybe later I’ll try and figure out how this can be done in bulk. I know there’s a way of doing it but I’ll just try and figure it out at a later time. thanks for your help.

6

This post explains exactly how to do it. It’s not easy, but it’s the way