Google Sheets API in TypeScript: Setup and Hello world
Tags: googlesheet, typescript, googlecloud
Published: 2021-10-08 08:15:12

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n4sn9p837aah2jtjr6s1.jpg)

# 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vpf2cwecn3qluc8h7tjd.png)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vwd1gulf1j2tye7jr519.png)

Search and select "Google Sheet API" and click "ENABLE" button.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o1jyy6zbzv243vld1lo6.png)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/18xwbfwix4vd7mpolzt1.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aeztf49kotr7z8iy9llk.png)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gukiysrxhcpkf81jzq06.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pmn1dmp1hg78v7iblqqc.png)

In the next page, you should name your service account. Give is a name whatever you like and click "DONE".
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b02ck1rcxwhwf2wcsubt.jpg)

# 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7sizigetoolfyg4ow5hr.jpg)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nlwe55uyh7exwlinqc38.png)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/itmny6vr7y5n29azk2pv.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vuwud9eqifv6v7h8z97m.png)

# 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
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/beqf33oo0qso4zo76ebo.jpg)

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](https://www.npmjs.com/package/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.

```typescript
;(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 !

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iwzni6x433oqblgygtwc.png)


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