DEV Community

Cover image for Google Sheets API in TypeScript: Setup and Hello world
Ryo Kuroyanagi
Ryo Kuroyanagi

Posted on

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.

Alt Text

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.

Alt Text
Alt Text

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

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.
Alt Text
Alt Text

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.
Alt Text

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

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.
Alt Text
Alt Text
Alt Text

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

Alt Text

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
Enter fullscreen mode Exit fullscreen mode

Alt Text

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
Enter fullscreen mode Exit fullscreen mode

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"]]
    }
  })
})()
Enter fullscreen mode Exit fullscreen mode

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

Alt Text

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

Latest comments (2)

Collapse
 
stelianok profile image
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!

Collapse
 
paganaye profile image
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');