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()

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! 🤖️⚡️

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()