Category: Python

bookmark_borderGoogle Sheets API – batchUpdate

Posted on by admin

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

Google AppScript 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

bookmark_borderMy impressions about Python

Posted on by admin

I didn’t (still don’t) like the fact very much that white space is part of Python’s syntax. Unless you have an editor which takes care automatically this can be quite frustrating. In the beginning, before using Visual Code, I used a simple text editor. And, as you may imagine, it was not always clear if tabs or spaces had been used. Python however is very strict on using same white space and same amount of white space when indenting.

I did have my problems referencing other modules in other directories, lost quite some hair getting this right and I still don’t think I’m doing it the perfect way.

Python did it’s job for my Trading Bot quite well. I like that it’s, unlike NodeJS, very much synchronous.

If feels rather lightweight, easy to install additional modules. On macOS however I run, not often, into problems with conflicts between different versions. With a little tweaking here and there (Path in console settings etc) I could get everything to work as I wanted

The biggest strength of Python I guess is handling strings and arrays. You can slice arrays in a multitude of ways.

I couldn’t get my head around creating a pip package though. This was way easier to achieve in NodeJS with npm.

Dealing with date/times and date/time differences is rather a pain, at first at least. Do some tests and you know how it works.

I never understood what I had to give self as first argument in a class method. That it can reference itself? Why not build that into the compiler? You cannot copy a method outside a class and paste it into the class without adjusting the signature.

def test(self):
    print("test")

Another thing I really was impressed with were column and row factories on db objects. You could literally create column_factory or row_factory to retrieve exactly the format from the db you needed. It was a bit complicated to setup my factories properly, but then it was just heaven. No need to “reformat” whole arrays or whatever. Just do some tests to see the results.

conn.row_factory = lambda cursor, row: row[0]  # will return data as a list

Another fantastic thing I’ve never seen in another programming language was __getattr__. This way you can “access” (or prevent access for that matter) properties of an object which are not defined as such, ie do not exist.

def __getattr__(self, name):
    return name.upper() + " does not exist"

Another, sometimes hand option, is to be able to return more than one value from a method. And you don’t need to adjust the signature, as the signature doesn’t define what’s returned.

def test():
    return 1, 2

Python, I think, I only used very basic modules, has a freaking great support for statistics and number crunching. There are different frameworks which achieve different stuff, down to neural learning.

The __del__ method as destructor exists, but I run into circumstances where it wasn’t always called. But if you use the concept of a ContextManager, you can basically achieve something similar, like a tear down (or destruct) method. I think on keyboard interrupts (depending on which ones) __del__ didn’t execute anymore. But by using the ContextManager this worked.

class ContextManager():

    def __enter__(self):
        pass

    def __exit__(self, ex_type, ex_value, ex_traceback):
        logic.tearDown()
        print("exit")

and
with ContextManager() as ct:
    ticker_ws.run_forever(sslopt={'cert_reqs': ssl.CERT_NONE})

In this example when the web socket client (ticker_ws) shuts down, the ContextManager is informed (exit) and you can run a method on your objects.

Some special types have weird names, see above (__exit__ or __enter__ or __name__ or __main__)

Using Visual Code IDE it’s rather easy to setup a debugging environment.

Verdicts

If you run just background tasks, lot’s of string/array editing and/or statistical analysis or other number crunching, then I think Python is quite a valid programming language