Let's Read Google Sheets via API!

This is a re-publication of an article I wrote on another site last year.

It's super easy. I'll explain how to read information written in Google Sheets using an API. With a little application, it can also be used as a simple CMS! It's super easy to use, so please take a look.

Let's Create a Spreadsheet

It's obvious, but first you need to create a spreadsheet. To make it easier to understand, I recommend creating it as follows.

$$
\begin{array}{|c|c|c|c|c|} \hline
いつ（yyyy-mm) & どこで & 何を & どのように & リンク \ \hline
2023-08 & 東京で & すしを食べた & もぐもぐ。 & url \ \hline
2022-05 & マレーシアで & ナシレマを食べた & スプーンを使って食べた & url \ \hline
\end{array}
$$

I wanted to create a page like a diary, so I made it this way. You might think, "Will the top row be read as well?" but it can be configured not to read it, so it's okay.

Let's Try Using the API

Enable Google Sheets API

First, enable the Google Sheets API from the following site.
Google Cloud console

Click the enable button to turn it on.

Create an API Key

Go to the "API & Services" page, and create new credentials.

Click "Create API Key". Now everything is ready! Just send a request to the API!

Sending a Request to the API

The Google Sheets API can be used by sending a request to sheets.googleapis.com. Below is the link including the necessary items for your retrieval. Please adjust it according to your own sheet.

<https://sheets.googleapis.com/v4/spreadsheets/{SheetID}/values/{SheetName!Start:End}/?key={APIKey}>

Done! Let's Send the API Request!

When you send the completed API request, the following message will appear on your screen. If it displays, you've succeeded! Yay!

{ "range": "Japanese!A2:F10", "majorDimension": "ROWS", "values": [ [ "2023-08", "東京で", "チキンを食べた", "もぐもぐ", "https://google.com", "bg-green" ] ] }