Google Sheets API in TypeScript: Setup and Hello world

I show you how to write text in a Google Sheets with TypeScript! The setting is a bit complicated. I hope this help beginners of Google Sheets API in TypeScript😉

Create a GCP project and issue a credential

To use Google Sheets API, we have to create a Google Cloud project and a service account with a permission of Google Sheet API. So please create a project in your GCP console.

Enable Google Sheet API in your project and create a service account

To enable Google Sheet API, go to the Dashboard page of "API & Services" and click "ENABLE APIS AND SERVICES" button.

Search and select "Google Sheet API" and click "ENABLE" button.

Credentials page should automatically open. Click "CREATE CREDENTIALS" button and select "Help me choose". The page is equivalent to the Credentials page in "APIs & Services" so please find the page if you closed the page accidentally.

The first step of "Create credentials" should start. Select Google Sheets API in "Which API are you using?" section if it's not selected automatically, and select "Application Data" in "What data will you be accessing?" section. And then, select "No, I'm not using them" in "Are you planning to use this API with Compute Engine, ..." section. Click "NEXT" button.

In the next page, you should name your service account. Give is a name whatever you like and click "DONE".

Get a credential of the service account

After creating a service account you should see it in a list of Service Accounts. Click the pencil icon and open the detail page. Select "KEYS" tab in the top row of the page and click "ADD KEY" and select "Create new key". And then, select JSON type and click "CREATE". A credential JSON file should be downloaded automatically. We use some values in the JSON file later.

The JSON should look like this. We use client_email and private_key in our coding step.

Create a Google Sheet and add the service account as Editor.

Please create a Google Sheet and add the service account email address as an "Editor" like you add a person to share a sheet. The email address is included in the downloaded JSON file. The field name is client_email.
Additionally, please take the sheet ID from the URL of your sheet. The URL should look like this and [YOUR_SHEET_ID] is the ID you should extract.

https://docs.google.com/spreadsheets/d/[YOUR_SHEET_ID]/edit

Setup is done!😉 Let's go to coding in TypeScript.

Append a row to your sheet by code

You might be tired a bit because of the former steps but coding begins from now on.

Create a Node.js project by npm init or yarn init and install googleapis and other necessary node modules. If you want to use JavaScript, you do not need ts-node, @types/node and typescript.

yarn add typescript ts-node @types/node googleapis

or

npm install typescript ts-node @types/node googleapis

This is the code to append a row to your sheet. For SERVICE_ACCOUNT_EMAIL and SERVICE_ACCOUNT_PRIVATE_KEY, please use your own values. They correspond to client_email and private_key in the JSON file respectively. SHEET_ID should be your sheet ID that you've gotten in the former step.

;(async () => {
  const auth = new google.auth.JWT({
    email: SERVICE_ACCOUNT_EMAIL,
    key: SERVICE_ACCOUNT_PRIVATE_KEY,
    scopes: ["https://www.googleapis.com/auth/spreadsheets"]
  })
  const sheet = google.sheets("v4")
  await sheet.spreadsheets.values.append({
    spreadsheetId: SHEET_ID,
    auth: auth,
    range: "Sheet1",
    valueInputOption: "RAW",
    requestBody: {
      values: [["hello", "world"]]
    }
  })
})()

Run the code and you should see hello and world in A and B column in the first row !

Congratulations 🎉 Now you got a new power to control Google Sheets by your code!

Comments

[Kauã Steliano]

It didn't workout for me initially, so i updated the authentication part with:


(async () => {
const auth = new google.auth.GoogleAuth({
keyFile: path.join(__dirname, '../../../', "credentials.json"),
scopes: ["https://www.googleapis.com/auth/spreadsheets"]
});

The tutorial helped me a lot! Thanks!

[Pascal GANAYE]

Excellent tutorial.
It worked well for me.
I only had to add this line at the top of the script before: ";(async () => {"...
const {google} = require('googleapis');