M
M
mrk_sn2021-08-16 16:27:07
Python
mrk_sn, 2021-08-16 16:27:07

How to combine multiple queries in Google Sheets into one?

I have been working with web requests not so long ago and the question arose
How to combine requests to change the contents of a cell so that it is one request?
I have a program that is supposed to build tables, but there is a lot of duplicate data in the tables. It was decided to "fix" this by painting the repeated mentions white (so that the filters work). So I was able to put the painting in one request. But since I could not figure out how to search for the numbers of the desired cells before creating the table, I did this by adding the ending ".white" to the duplicates and then removing this ending, remembering the number and coloring.
How can content change requests be combined?

for line in resUsed:
            white = white + ws.findall(line + ".white")

    update = []
    for line in white:
        lineStr = re.findall("\d+", str(line))
        lineStrA1 = gspread.utils.rowcol_to_a1(lineStr[0], lineStr[1])
        name = str(line).split("'")
        if name[-2] != "":
            ws.update(lineStrA1, name[-2].replace(".white", ""))
            time.sleep(1)
        resName = ""

        lineStrA1 = gspread.utils.a1_range_to_grid_range(lineStrA1  + ":" + lineStrA1, sheet_id= 0)

        reguests.append({
                    "repeatCell": {
                        "range": lineStrA1,
                        "cell": {
                            "userEnteredFormat": {
                                "borders":{
                                    "top":{
                                        "style": "SOLID",
                                        "color": {
                                            "red": 1.0,
                                            "green": 1.0,
                                            "blue": 1.0
                                        }
                                    }
                                },
                                "textFormat": {
                                    "foregroundColor": {
                                        "red": 1.0,
                                        "green": 1.0,
                                        "blue": 1.0
                                    }
                                }
                            }
                        },
                        "fields": "userEnteredFormat.textFormat, userEnteredFormat.borders"
                        }
                    })

    results = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={
        "requests": reguests
    }).execute()


And these queries need to be combined
if name[-2] != "":
            ws.update(lineStrA1, name[-2].replace(".white", ""))

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question