Google Sheets API – batchUpdate

batchUpdate() works on values if you use sheet.values().batchUpdate() and works on sheet properties if you use sheet..batchUpdate()

So I wanted to be notified for my tradingBot (made in Python), if a certain KPI was met. On my Mac, no problem. I use Notify.

from notifypy import Notify

But I wanted to receive a notification even when I am not sitting at my computer. I figured with the help of Google I could achieve this. So I write to a Google Sheet some values and there I trigger an AppScript which sends me an email if KPI is met.

First attempt:

    values = [[datetime, last, change, gain, loss, rsi]]
    part1 = {
        'values': values
    }

    result = sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A2:F2",
        valueInputOption="RAW", body=part1).execute()


    values = [["insert into ticker (datetime, last, change,gain,loss) values ( '{}',{:f} ,{:f} ,{:f}, {:f} )".format(
        datetime, last, change, gain, loss, rsi)]]
    part2 = {
        "values": values
    }
    result = sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A5",
        valueInputOption="RAW", body=part2).execute()

execute() was called twice, for each row I update. Hence triggering the AppScript (which sends an email) twice. I always wondered why I receive duplicates. Couldn’t figure it out.

Then I received a warning from Google: too many emails sent, with some sort of Log

So I saw: aha, it actually calls the “myFunction” twice. I immediately knew it had to do with how I update the sheet.

I quickly figured there’s a “batchUpdate” function. But documentation was awfully bad. Sheets does know 2 different kind of requests. One kind updates the sheet’s properties (colours/rows, design stuff). The other updates it’s values. BUT they don’t tell that very explicitly. By luck I figured it works like this:

    values = [datetime, last, change, gain, loss, rsi]
    part1 = {
        "range": "Sheet1!A2:F2",

        "values": [
            values
        ]
    }

    values = [["insert into ticker (datetime, last, change,gain,loss) values ( '{}',{:f} ,{:f} ,{:f}, {:f} )".format(
        datetime, last, change, gain, loss, rsi)]]
    part2 = {
        "range": "Sheet1!A5",
        "values": values
    }

    requests = {
        "valueInputOption": "RAW",
        "data": [

            part1, part2

        ]
    }

    sheet.values().batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                               body=requests).execute()

and the important part is the “.values()” part in the last line. I first tried without it and Google told me: don’t know range, don’t know data, don’t find fields and other stuff.

As soon as I added the .values() it worked seamless

Looking forward to your comments