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.
| Param | Type | Default | Description |
|---|---|---|---|
| googleServiceAccountCredentials | string | Deno.env.get('GOOGLE_SERVICE_ACCOUNT_CREDENTIALS') | Credentials to use for authentication |
| scope | array | ['spreadsheets.readonly'] | The permissions granted to interact with the resource |
| spreadsheetId | string | Deno.env.get('GOOGLESHEETS_SPREADSHEET_ID') | The spreadsheet ID |
Examples
The following examples assume you have created an api client instance.
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>()