Google Sheets

Write data to a specified Google Spreadsheet

Spreadsheet ID

The target Google Spreadsheet ID to write data to (supporting %FILENAME% variable). The ID can be obtained from the browser's address bar, for example:

https://docs.google.com/spreadsheets/d/[SpreadsheetID]/edit#gid=901783423docs.google.com

API

In addition to the commonly used file handling synchronous functions like api.files(), api.read(filename, encoding='utf8'), api.write(filename, text), and api.remove(filename), you can also use the synchronous function api.sheetNames() to retrieve an array of sheet titles from a spreadsheet:

// Print the titles of all the sheets
console.log(api.sheetNames())

Asynchronous functions include api.addSheet(header, rows, name), api.delSheet(name), api.getRows(name), api.addRows(name, rows), api.delRow(name, rowIndex), api. getCell(name, rowIndex, colIndex), api.setCell(name, rowIndex, colIndex, value), api.loadSheet(name, range):

// Create a new spreadsheet titled "new sheet"
let header = ['Name','Email']
let rows = [
  {'Name':'Alice','Email':'[e-mail]'},
  {'Name':'Bob','Email':'[e-mail]'}
]     
await api.addSheet(header, rows, 'new sheet')

// Delete a spreadsheet titled "old sheet"
await api.delSheet('old sheet')

// Add several rows of data to a sheet with the title "new sheet"
let rows2 = [{'Name':'Candy','Email':'[e-mail]'}]
await api.addRows('new sheet', rows2)

// Read the spreadsheet data titled "new sheet"
let rows3 = await api.getRows('new sheet')
console.log(rows3)

// Delete the third column, excluding the header column, and column count incrementing from 0
await api.delRow('new sheet', 2） // row of 'Candy' will be deleted

//Read a cell in a Google Spreadsheet, including the header row, with row count and column count incrementing from 0
let cell = await api.getCell('new sheet', 2, 0)
console.log(cell) // 'Bob'

// Write data to a cell in a Google Spreadsheet, including the header row, with row count and column count incrementing from 0
await api.setCell('new sheet', 2, 0, 'Bob Wang')

// Batch read and write cells and then save the changes in the range from A1 to E5 in a Google Spreadsheet
let sheet = await api.loadSheet('sheet1', 'A1:E5')
let cell1 = await sheet.cell(0,1)
cell1.value = 'Jerry'
let cell2 = await sheet.cell(1,1)
cell2.value = 'New Taipei City'
await sheet.save()

--

We are dedicated to improving our content. Please let us know if you come across any errors, including spelling, grammar, or other mistakes, as your feedback is valuable to us! 🤖️⚡️