When Google first produced Sheets I liked it - but was a little disappointed: it really didn’t match up to Excel. That was too much to expect - but the distance was vast. That was back when Microsoft was the enemy of real programmers. Well things have changed: Sheets has all the features that it turns out you need and includes one big plus vs Excel: Javascript rather than VBA.
Sheets Can Be a UI and IDE
The horrible reality of banking technology is that it somehow revolves around Microsoft Excel and gigabytes of wild VBA. It’s one of those rites of passage for programmers joining the front office of a bank: “here’s our pricing spreadsheet. We need some changes…” and you are confronted with a history of dubious hacking and workarounds. “Soul destroying” is a reasonable description of this rookie period. But - Excel with VBA is actually a genuine programming skill that pays well and is in high demand.
Sheets with Javascript is a different feeling entirely - somehow. In Sheets’ Tools menu you choose Script editor. It brings up a reasonable IDE with a debugger. The functions you create here are immediately available in your sheet as formula invokded with =FunctionName() in the usual way. As soon as you save it applies the changes to the sheet - quick turnaround - which is very convenient… or so you might think.
In my case I was trying to create a Facebook audience from client data. Facebook likes to have location detail to better their chance of a match - they want City and State, and Country two-letter code (ct, st, country). Unfortunately I just had a free text field where users had entered their location: sometimes just a city, sometimes a country, sometimes city and state etc - sometimes with commas sometimes without. How to get this data into the form I needed across 13000 rows?
It turns out the Google Places API solved this problem easily. It does a pretty good job of converting any kind of place data such as a name, address, or phone number into city, state and country - just what I needed - with just 2 API calls.
To use it, you have to sign up with a card to get an API key. On sign up it gave me $300 free usage - “wow” I thought - “not a bad incentive - should last me months!”. I assumed that their target was corporate accounts. They wanted to give a good deal like this to wean people off Excel, then make money once they get thousands of users onboarded. So I thought.
These APIs return standard JSON given an appropriate URL with the parameters in the typical form: https://maps.googleapis.com/maps/api/place/textsearch/json?query=London+England&key=YOUR_API_KEY - passing the place to search for (London England) and an API key. There’s some documentation on the returned JSON but in teasing out the correct approach to my particular data - and as is the norm in software development - trial and error is the most effective way to get the right format.
I realized the Places API returns a place_id you can use with the Place Details API with appropriate parameters to get the specific data: https://maps.googleapis.com/maps/api/place/details/json?place_id=ChIJdd4hrwug2EcRmSrV3Vo6llI&fields=address_component&key=YOUR_API_KEY.
So in this call the fields parameter is used to restrict the returned JSON to the address_component. This provides JSON containing the City in locality, State in administrative_area_level_1 and the Country. I can take the long_name for the first two and short_name for country to match Facebook’s requirements.
{
// ...
"address_components": [
{
"long_name": "London",
"short_name": "London",
"types": [ "locality", "political" ]
},
// ...
{
"long_name": "England",
"short_name": "England",
"types": [ "administrative_area_level_1", "political" ]
},
{
"long_name": "United Kingdom",
"short_name": "GB",
"types": [ "country", "political" ]
}
// ...
]
// ...
}
I now have a good proportion of 13000 client’s locations correctly in Facebook format ready for upload. Result!
But… looks like I have a few emails… I should check my emails more often.
“100% of budget reached” - which was odd, given I didn’t recall setting up any budget - and the budget amount passed was $125. Really? Did I somehow spend that much? Unlikely… but…
“Payment received”… “Your payment of £500.00 was applied to Google Cloud Platform & APIs on Jul 9, 2020”.
I checked my bank account. Indeed, £500 - five hundred pounds - had been extracted from my bank account! WTF!!!
So I went to my account page: https://console.cloud.google.com/billing?organizationId=12345678910123&supportedpurview=project and low and behold…
Yeah…
£1,564.21 charged at that point - and £500 already paid out!
How It Happened
What I’ve tried to make clear here is that I followed a reasonable programming practice, and used Sheets in the way it’s typically used: wrote a function, put it in new calculated column with a formula calling the function, and filled down.
It turns out that the “convenient feature” of Script Editor I mentioned previously is the problem. It applies - and immediately executes - your changes every time you save the script. My progressive tuning of the script, observing the results as they appeared in the sheet - very much a normal iterative approach I would say, combined with the initial fill down led to it repeatedly calling those two APIs - in total: 139278 times…
Based on the fact that Maps itself is free - with a little advertising - just like most Google consumer products, I’d assumed that the Google API was “just about free”, but that is not the case. The pricing is quite high in fact - Places is $17 per 1000 calls - remember I was making a Find Places search and a Places Detail for each of my 13000 rows, and as I experimented, iterating through multiple adjustments of my script and checking the results, 26000 calls were being made for each save.
When I signed up, this was all made plain in the associated agreement. Obviously, I didn’t read this nor check the pricing. Okay, I’m foolish, but I nevertheless assert that it is very easy to fall in this trap in circumstances like this.
Google makes available a number of features to help manage costs and set limits to avoid situations like mine. I’m not sure its fair to say that they don’t provide enough warning. Perhaps there should be more.
With care, the APIs can be used safely: set those limits and alerts. The initial free $300 is topped up to the tune of $200 every month so - with that care - Google API usage can be managed.
Personally, I feel the problem - if there is one - is in fact the pricing: it’s too high. Lowering it - perhaps by a factor of 1000 - would encourage customers to experiment and be creative in their usage. They would likely make interesting new products on top of Places and other APIs. They could perhaps tune it over time from that level.
The moral of the story is that if you put your card down read those agreements! Keep on top of what your tools are doing for you in the background. Regularly read your emails…
If you don’t do the above then - you need to beg, which is what I did. First I begged to Google Cloud Support: they were polite, but then worryingly silent. So I went to their Facebook page, and Messaged them. I’m not sure what the outcome would have been if I hadn’t done this - maybe the same - but I think it’s a reasonable step since there you talk to someone typically with a PR, rather than technical background. The representative I talked to contacted Cloud Support and ensured me my case would be looked at. They were responsive and friendly.
In the end my usage was accepted as “accidental” and my charges were zeroed. The £500 that had been charged was returned to my account. I very much thank Google Cloud for this!
Remember: check the pricing; keep aware of when APIs are called; put billing limits in place.