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:

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':'alice@mail.com'},
  {'Name':'Bob','Email':'bob@mail.com'}
]     
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':'candy@mail.com'}]
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()

PreviousGoogle Drive NextGoogle OCR

Last updated 1 year ago

// Create a new spreadsheet titled "new sheet" let header = ['Name','Email'] let rows = [ {'Name':'Alice','Email':'alice@mail.com'}, {'Name':'Bob','Email':'bob@mail.com'} ] 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':'candy@mail.com'}] 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()