Google Sheets API-Hell

Octavia C.
Jan 7 · 6 min read

Google Sheets API owes me three days of my life.

Say you’re a web dev in training, just starting out. The briefing of an exercise mentions using Google Sheets API to store and display reviews about a fictional restaurant, whose webpage you’re creating. It’s just like an excel sheet, right? It’s just a couple of “post” and “get”s on some rows, right?

Donald Trump saying “wrong”.
Donald Trump saying “wrong”.
You tell ’em Donnie (source: giphy)

First impressions

Note: It is entirely possible that this is not hard at all and I’m just very new, but at the least I hope my misadventures will be a fun read.

I was young and hopeful, and I had been coding for a handful of months. This was our first time working with API’s and I only half understood what they were and how to call them, GET and POST were magical words, but I knew it had to do *something* with a URL.

Harry Potter waving wand.
Harry Potter waving wand.
Accio DATASET! (source: giphy)

I was lulled into a false sense of security when first landing on the documentation, because a few clicks down the line, I saw a quickstart guide. Easy enough, I copy-paste things without understanding everything like the good beginner I am, and sure enough it retrieves some test data into my CLI.

May he who never copied and pasted code he didn’t understand cast the first stone.

Then it got complex

The next step was making sure that I could access the rows I had populated my google sheets with. I go for multiple ranges, since I have several lines I need to retrieve and place in the website. This is a crucial error, as I later understood that “multiple ranges” is for different disjointed ranges of data, as long as it is continuous, use a single range. Maybe I should have known, maybe I’m dumb, maybe this is useful information that’s lacking from the documentation. Also, I thought I needed URL-looking stuff for this but the documentation provides me with some PHP code instead. Where is the GET URL I could try in Postman? Who knows. Have some code instead.

Hah.

Hahah.

(My mother always told me not to use ambiguous variable names also how many times can you use “range” in one snippet of code anyways.)

Unsurprisingly, this time copy-paste doesn’t work, which is quite normal since there is no “$service” variable instantiated in my code. What is this $service? What is this $ranges? WHAT IS LIFE?

To investigate, I click on the “spreadsheets.value.batchGet” link above.

We must go deeper

Which leads me to this beautiful page.

Please note that we’re no longer in Texas, as we’ve switched from guides to reference and what even is this structure.

You get a Google-home-brewed-Postman-like thing to test your API and wonders above wonders now suddenly there is something about Authentication. I had no idea about what that was and how it worked, it had been mentioned nowhere up to now, suddenly I do get a URL-like API request thing which was what I was looking for in the first place, God only knows how the PHP code snippet relates to this, I still don’t know what $service is or does.

The next hours were spent trying to understand Authentication. Turns out you have to allow access through Google OAuth to the sheet and I’m too much of a noob to get how it truly works. To me it almost looks to me like you’d need to have the credentials of the person on whose Google Drive the sheet is located because otherwise you don’t get read-write access, so please give me your Google log-in details mister customer, haha, that can’t possibly be true, right?

Right?

(source: giphy)

Even deeper.

Further down that same page we get an example of code. Now, I’m not sure what example means in English for the fine people at Google, but to me, it’s an illustration of something you explain. Implying you need to have an explanation somewhere before or after the example, which was not the case here, as the philosophy was more “just figure stuff out by reading this code”.

Hooray, here comes $service!

Please pay attention to the underlined link. The function you need to instantiate a $client variable, which you need for $service, is in the quickstart / tutorial thing. In the effin’ tutorial.

Where if you copy-paste, there is this bit:

Which makes sure it doesn’t work because it needs to be called in your command line.

By the time I had reached this point, I was sort of rocking back and forth in a corner in desperation, so realizing I needed to remove this thing also cost me about half an hour. Not that stuff worked after that.

In part because of this:

I didn’t figure the solution to this out by myself, as I was sitting together with a BeCode coach and a co-student, everyone being baffled. The token.json for the authentication needs to have certain permissions so we ended up chmoding the eff out of it and just allowing reading, writing, executing (of heretics), and the buying of my soul.

We were almost 1.5 days in, nothing worked. However I had not given up.

Errors, errors everywhere.

I kept coding with code I didn’t get which is HARD, so I kept having errors with functions or things that were in the Google library. I eventually decided to ventured into the abyss of the Vendor library folder.

Where I met this:

Don’t do this.

Eventually, I reached the function in the code that kept throwing an error in this beautiful, beautiful code.

Beautiful.

If only the param entry was the only thing getting abused.

BEAUTIFUL

Fun game: count the “params” or parameter-related variable names.

BEAUTIFUL CODE

You and me both, Google library

What happened then

After three days, I made it work. The layout of my website was/is a disaster, I was a disaster, I had lost about 2 years in life expectancy due to sheer frustration, but the couple of rows from the Google sheet were displayed on my webpage.

Accurate representation of my mental state. (source: giphy)

The blood, sweat and code resulting from this exercise can be marveled at here.

TL;DR: Anyhow, don’t be like me, use Sheety.

BeCode

BeCode: Learn to code, get a job !

Octavia C.

Written by

Dual class human pharmacist/webdev in training. Also friendly GM next door.

BeCode

BeCode

BeCode: Learn to code, get a job !

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade