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!
Top comments (2)
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!
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');