Netzo Logo

googlesheets

Google Sheets is a spreadsheet program included as part of a free, web-based software office suite.

Usage

import { googlesheets } from 'netzo/apis/googlesheets.ts'

const {
  api,
  resultToRows
} = googlesheets({
  googleServiceAccountCredentials: Deno.env.get('GOOGLE_SERVICE_ACCOUNT_CREDENTIALS'),
  scope: ['spreadsheets.readonly'],
  spreadsheetId: Deno.env.get('GOOGLESHEETS_SPREADSHEET_ID'),
})

Configuration

The googlesheets factory function expects an object with the following, and returns an object with an API client api and a useful function.

ParamTypeDefaultDescription
googleServiceAccountCredentialsstringDeno.env.get('GOOGLE_SERVICE_ACCOUNT_CREDENTIALS')Credentials to use for authentication
scopearray['spreadsheets.readonly']The permissions granted to interact with the resource
spreadsheetIdstringDeno.env.get('GOOGLESHEETS_SPREADSHEET_ID')The spreadsheet ID
Refer to official API documentation for more information.

Examples

The following examples assume you have created an api client instance.

The resultToRows function transforms your data into key-value pairs with column headers as keys. The function assumes the first row of your sheet holds the headers. If this is not the case, you should pass the headers as the second argument to the function in string[] format, e.g. ['name', 'lastname', 'email']

Get rows

Get all rows within the specified range.

The resultToRows function returns your data as key-value pairs, assuming the top row holds the headers. Find out how to pass custom headers.

const result = await api.values[RANGE].get<ValueRange>()
const resultData = result.values
const rows = resultToRows(result)

Get row

Get one row of the spreadsheet by specifying the index.

The example returns the first row of data, assuming the top row holds the headers. The resultToRows function returns your data as key-value pairs. Find out how to pass custom headers.

const index = 1
const result = await api.values[RANGE].get<ValueRange>()
const resultData = result.values[index]
const row = resultToRows(result)[index - 1]

Add row

Add a row to the spreadsheet.

Be sure to keep insertDataOption as INSERT_ROWS, or existing data in your range will be overwritten.

const query: QueryAddOrUpdateValues = {
  valueInputOption: 'USER_ENTERED',
  insertDataOption: 'INSERT_ROWS',
  includeValuesInResponse: true
}
const data: DataAddOrUpdateValues = { values: [['value1', 'value2', 'value3']] }

const result = await api.values[`${RANGE_TO_INSERT}:append`].post<AddValuesResult>(data, query)
const resultData = result.updates.updatedData.values

Update row

Update a row by specifying the range.

const query: QueryAddOrUpdateValues = {
  valueInputOption: 'USER_ENTERED',
  includeValuesInResponse: true
}
const data: DataAddOrUpdateValues = { values: [['updatedValue1', 'updatedValue2', 'updatedValue3']] }
const result = await api.values[RANGE_TO_UPDATE].put<UpdateValuesResult>(data, query)
const resultData = result.updatedData.values

Delete row

Delete a row by specifying the range.

const resultData = await api.values[`${RANGE_TO_DELETE}:clear`].post<DeleteValuesResult>()

References