Google Sheets

This demo uses node-google-spreadsheet to interact with Google Sheets v4 API.此演示使用node-google-spreadsheet与google Sheets v4 API进行交互。

Code that does not directly relate to SheetJS APIs are tucked away. 与SheetJSAPI没有直接关系的代码被隐藏起来。Click on the "click to show" blocks to see the code snippets.单击“单击以显示”块以查看代码段。

Initial Configuration初始配置

Install the dependencies:安装依赖项：

npm  i https://cdn.sheetjs.com/xlsx-latest/xlsx-latest.tgz [email protected]

The library README has a guide for configuring a service worker with write access to the document. 库自述有一个指南，用于配置具有文档写访问权限的服务人员。Following the service worker guide, the JSON key should be saved to key.json.根据service worker指南，JSON键应保存到key.json。

The following helper function attempts to authenticate and access the specified sheet by ID. 以下helper函数尝试通过ID验证和访问指定的工作表。The code should be copied and saved to common.js:代码应复制并保存到common.js：

Code (click to show)

common.js

const fs = require("fs");
const { GoogleSpreadsheet } = require('google-spreadsheet');

module.exports = async(ID) => {
  /* get credentials */
  const creds = JSON.parse(fs.readFileSync('key.json'));

  /* initialize sheet and authenticate */
  const doc = new GoogleSpreadsheet(ID);
  await doc.useServiceAccountAuth(creds);
  await doc.loadInfo();
  return doc;
}

Exporting Document Data to a File将文档数据导出到文件

The goal is to create an XLSB export from a Google Sheet. 目标是从谷歌表单创建XLSB导出。Google Sheets does not natively support the XLSB format. Google Sheets本机不支持XLSB格式。SheetJS fills the gap. SheetJS填补了这一空白。The last subsection最后一小节 includes detailed instructions for running locally.包括本地运行的详细说明。

Connecting to the Document连接到文档

This uses the common.js helper from above:这使用了上面的common.js助手：

Code (click to show)

/* Connect to Google Sheet */
const ID = "<google sheet id>";
const doc = await require("./common")(ID);

Creating a New Workbook创建新工作簿

XLSX.utils.book_new() creates an empty workbook with no worksheets:创建没有工作表的空工作簿：

/* create a blank workbook */
const wb = XLSX.utils.book_new();

Looping across the Document在文档中循环

doc.sheetsByIndex is an array of worksheets in the Google Sheet Document.是Google Sheet文档中的一组工作表。

Code (click to show)

/* Loop across the Document sheets */
for(let i = 0; i < doc.sheetsByIndex.length; ++i) {
  const sheet = doc.sheetsByIndex[i];
  /* Get the worksheet name */
  const name = sheet.title;
  /* ... */
}

Convert a Google Sheets sheet to a SheetJS Worksheet将Google Sheets工作表转换为SheetJS工作表

The idea is to extract the raw data from the Google Sheet headers and combine with the raw data rows to produce a large array of arrays.其思想是从谷歌表格标题中提取原始数据，并与原始数据行相结合，生成一个大型数组。

Code (click to show)

  /* get the header and data rows */
  await sheet.loadHeaderRow();
  const header = sheet.headerValues;
  const rows = await sheet.getRows();

  /* construct the array of arrays */
  const aoa = [header].concat(rows.map(r => r._rawData));

This can be converted to a SheetJS worksheet using XLSX.utils.aoa_to_sheet:可以使用XLSX.utils.aoa_to_sheet将其转换为SheetJS工作表：

  /* generate a SheetJS Worksheet */
  const ws = XLSX.utils.aoa_to_sheet(aoa);

XLSX.utils.book_append_sheet will add the worksheet to the workbook:将工作表添加到工作簿：

  /* add to workbook */
  XLSX.utils.book_append_sheet(wb, ws, name);

Generating an XLSB file生成XLSB文件

XLSX.writeFile will write a file in the filesystem:将在文件系统中写入文件：

/* write to SheetJS.xlsb */
XLSX.writeFile(wb, "SheetJS.xlsb");

How to Run Export Example如何运行导出示例

How to run locally如何在本地运行 (click to show)

0) Follow the Authentication and Service Account instructions. 遵循验证和服务帐户说明。At the end, you should have最后，你应该

• Created a project and enabled the Sheets API创建项目并启用图纸API
• Created a service account with a JSON key使用JSON密钥创建了服务帐户

Move the generated JSON key to key.json in your project folder.将生成的JSON键移动到项目文件夹中的key.json。

1) Create a new Google Sheet and share with the generated service account. 创建一个新的谷歌表单并与生成的服务帐户共享。It should be granted the "Editor" role它应该被授予“编辑”角色

2) Install the dependencies:安装依赖项：

npm i https://cdn.sheetjs.com/xlsx-latest/xlsx-latest.tgz [email protected]

2) Save the following snippet to common.js:将以下代码段保存到common.js：

common.js

const fs = require("fs");
const { GoogleSpreadsheet } = require('google-spreadsheet');

module.exports = async(ID) => {
  /* get credentials */
  const creds = JSON.parse(fs.readFileSync('key.json'));

  /* initialize sheet and authenticate */
  const doc = new GoogleSpreadsheet(ID);
  await doc.useServiceAccountAuth(creds);
  await doc.loadInfo();
  return doc;
}

3) Save the following snippet to pull.js:将以下代码段保存到pull.js：

pull.js

const XLSX = require("xlsx");

/* create a blank workbook */
const wb = XLSX.utils.book_new();

const init = require("./common");
const ID = "<google sheet ID>";

(async() => {

  const doc = await init(ID);

  for(let i = 0; i < doc.sheetsByIndex.length; ++i) {
    const sheet = doc.sheetsByIndex[i];
    const name = sheet.title;

    /* get the header and data rows */
    await sheet.loadHeaderRow();
    const header = sheet.headerValues;
    const rows = await sheet.getRows();
    const aoa = [header].concat(rows.map(r => r._rawData));

    /* generate a SheetJS Worksheet */
    const ws = XLSX.utils.aoa_to_sheet(aoa);

    /* add to workbook */
    XLSX.utils.book_append_sheet(wb, ws, name);
  }

  /* write to SheetJS.xlsb */
  XLSX.writeFile(wb, "SheetJS.xlsb");

})();

4) Replace <google sheet ID> with the ID of the actual document.用实际文档的ID替换<google sheet ID>。

5) Run node pull.js once. 运行一次node pull.js。It will create SheetJS.xlsb.它将创建SheetJS.xlsb。

6) Open SheetJS.xlsb and confirm the contents are the same as Google Sheets.打开SheetJS.xlsb，确认内容与Google Sheets相同。

7) Change some cells in the Google Sheets Document.更改Google Sheets文档中的一些单元格。

8) Run node pull.js again and reopen SheetJS.xlsb to confirm value changes.再次运行node pull.js并重新打开SheetJS.xlsb以确认值更改。

Updating a Document from a Local File从本地文件更新文档

The goal is to refresh a Google Sheet based on a local file. 目标是根据本地文件刷新谷歌表单。 The problem can be broken down into a few steps. 这个问题可以分解为几个步骤。The last subsection includes detailed instructions for running locally.最后一小节包括本地运行的详细说明。

Reading the Workbook File读取工作簿文件

XLSX.readFile can read files from the filesystem. 可以从文件系统读取文件。 The following line reads sheetjs.xlsx from the current directory:以下行从当前目录中读取sheetjs.xlsx：

const XLSX = require("xlsx");
const wb = XLSX.readFile("sheetjs.xlsx");

Connecting to the Document连接到文档

This uses the common.js helper from above:这使用了上面的common.js助手：

Code (click to show)

/* Connect to Google Sheet */
const ID = "<google sheet id>";
const doc = await require("./common")(ID);

Clearing the Document清除文档

Google Sheets does not allow users to delete every worksheet. Google Sheets不允许用户删除每个工作表。The snippet deletes every worksheet after the first, then clears the first worksheet.该代码段删除第一个工作表之后的每个工作表，然后清除第一个工作表。

Code (click to show)

/* clear workbook */
{
  /* delete all sheets after the first sheet */
  const old_sheets = doc.sheetsByIndex;
  for(let i = 1; i < old_sheets.length; ++i) {
    await old_sheets[i].delete();
  }
  /* clear first worksheet */
  old_sheets[0].clear();
}

Update First Worksheet更新第一个工作表

In the SheetJS workbook object, worksheet names are stored in the SheetNames property. 在SheetJS工作簿对象中，工作表名称存储在SheetNames属性中。The first worksheet name is wb.SheetNames[0]:第一个工作表名称是wb.SheetNames[0]：

const name = wb.SheetNames[0];

The Sheets property is an object whose keys are sheet names and whose values are worksheet objects.Sheets属性是一个对象，其键是图纸名称，其值是工作表对象。

const ws = wb.Sheets[name];

In the Google Sheet, doc.sheetsByIndex[0] is a reference to the first sheet:在Google工作表中，doc.sheetsByIndex[0]是对第一个工作表的引用：

const sheet = doc.sheetsByIndex[0];

Update Sheet Name更新工作表名称

The worksheet name is assigned by using the updateProperties method. 使用updateProperties方法指定工作表名称。The desired sheet name is the name of the first worksheet from the file.所需的工作表名称是文件中第一个工作表的名称。

/* update worksheet name */
await sheet.updateProperties({title: name});

Update Worksheet Data更新工作表数据

sheet.addRows reads an Array of Arrays of values. 读取值数组的数组。XLSX.utils.sheet_to_json can generate this exact shape with the option header: 1. 可以使用选项header: 1生成此精确形状。Unfortunately Google Sheets requires at least one "Header Row". 不幸的是，Google Sheets至少需要一个“标题行”。This can be implemented by converting the entire worksheet to an Array of Arrays and setting the header row to the first row of the result:这可以通过将整个工作表转换为数组并将标题行设置为结果的第一行来实现：

/* generate array of arrays from the first worksheet */
const aoa = XLSX.utils.sheet_to_json(ws, {header: 1});

/* set document header row to first row of the AOA */
await sheet.setHeaderRow(aoa[0]);

/* add the remaining rows */
await sheet.addRows(aoa.slice(1));

Add the Other Worksheets添加其他工作表

Each name in the SheetJS Workbook SheetNames array maps to a worksheet. SheetJS工作簿SheetNames数组中的每个名称都映射到一个工作表。The loop over the remaining worksheet names looks like其余工作表名称上的循环如下所示

for(let i = 1; i < wb.SheetNames.length; ++i) {
  /* wb.SheetNames[i] is the sheet name */
  const name = wb.SheetNames[i];
  /* wb.Sheets[name] is the worksheet object */
  const ws = wb.Sheets[name];
  /* ... */
}

Appending a Worksheet to the Document将工作表追加到文档

doc.addSheet accepts a properties object that includes the worksheet name:接受包含工作表名称的properties对象：

  const sheet = await doc.addSheet({title: name});

This creates a new worksheet, sets the tab name, and returns a reference to the created worksheet.这将创建一个新的工作表，设置选项卡名称，并返回对所创建工作表的引用。

Update Worksheet Data更新工作表数据

This is identical to the first worksheet code:这与第一个工作表代码相同：

  /* generate array of arrays from the first worksheet */
  const aoa = XLSX.utils.sheet_to_json(ws, {header: 1});

  /* set document header row to first row of the AOA */
  await sheet.setHeaderRow(aoa[0]);

  /* add the remaining rows */
  await sheet.addRows(aoa.slice(1));

How to Run Update Example如何运行更新示例

How to run locally如何在本地运行 (click to show)

0) Follow the Authentication and Service Account instructions. 遵循验证和服务帐户说明。At the end, you should have最后，你应该

• Created a project and enabled the Sheets API创建项目并启用图纸API
• Created a service account with a JSON key使用JSON密钥创建了服务帐户

Move the generated JSON key to key.json in your project folder.将生成的JSON键移动到项目文件夹中的key.json。

1) Create a new Google Sheet and share with the generated service account. 创建一个新的谷歌表单并与生成的服务帐户共享。It should be granted the "Editor" role它应该被授予“编辑”角色

2) Install the dependencies:安装依赖项：

npm i https://cdn.sheetjs.com/xlsx-latest/xlsx-latest.tgz [email protected]

2) Save the following snippet to common.js:将以下代码段保存到common.js：

common.js

const fs = require("fs");
const { GoogleSpreadsheet } = require('google-spreadsheet');

module.exports = async(ID) => {
  /* get credentials */
  const creds = JSON.parse(fs.readFileSync('key.json'));

  /* initialize sheet and authenticate */
  const doc = new GoogleSpreadsheet(ID);
  await doc.useServiceAccountAuth(creds);
  await doc.loadInfo();
  return doc;
}

3) Save the following snippet to push.js:将以下代码段保存到push.js：

push.js

const XLSX = require("xlsx");
const fs = require("fs");
/* create dummy worksheet if `sheetjs.xlsx` does not exist */
if(!fs.existsSync("sheetjs.xlsx")) {
  const wb = XLSX.utils.book_new();
  const ws1 = XLSX.utils.aoa_to_sheet([["a","b","c"],[1,2,3]]); XLSX.utils.book_append_sheet(wb, ws1, "Sheet1");
  const ws2 = XLSX.utils.aoa_to_sheet([["a","b","c"],[4,5,6]]); XLSX.utils.book_append_sheet(wb, ws2, "Sheet2");
  XLSX.writeFile(wb, "sheetjs.xlsx");
}
/* read and parse sheetjs.xlsx */
const wb = XLSX.readFile("sheetjs.xlsx");

const init = require("./common");
const ID = "<google sheet ID>";

(async() => {

  const doc = await init(ID);

  /* clear workbook */
  {
    /* delete all sheets after the first sheet */
    const old_sheets = doc.sheetsByIndex;
    for(let i = 1; i < old_sheets.length; ++i) {
      await old_sheets[i].delete();
    }
    /* clear first worksheet */
    old_sheets[0].clear();
  }

  /* write worksheets */
  {
    const name = wb.SheetNames[0];
    const ws = wb.Sheets[name];
    /* first worksheet already exists */
    const sheet = doc.sheetsByIndex[0];

    /* update worksheet name */
    await sheet.updateProperties({title: name});

    /* generate array of arrays from the first worksheet */
    const aoa = XLSX.utils.sheet_to_json(ws, {header: 1});

    /* set document header row to first row of the AOA */
    await sheet.setHeaderRow(aoa[0])

    /* add the remaining rows */
    await sheet.addRows(aoa.slice(1));

    /* the other worksheets must be created manually */
    for(let i = 1; i < wb.SheetNames.length; ++i) {
      const name = wb.SheetNames[i];
      const ws = wb.Sheets[name];

      const sheet = await doc.addSheet({title: name});
      const aoa = XLSX.utils.sheet_to_json(ws, {header: 1});
      await sheet.setHeaderRow(aoa[0])
      await sheet.addRows(aoa.slice(1));
    }
  }

})();

4) Replace <google sheet ID> with the ID of the actual document.用实际文档的ID替换<google sheet ID>。

5) Run node push.js once. 运行node push.js一次。It will create sheetjs.xlsx and update the sheet.它将创建sheetjs.xlsx并更新工作表。

6) Edit sheetjs.xlsx with some new data使用一些新数据编辑sheetjs.xlsx

7) Run node push.js again and watch the Google Sheet update!再次运行node push.js并观看谷歌表单更新！

Using the Raw File Exports使用原始文件导出

node-google-spreadsheet can download the XLSX or ODS export of the document. 可以下载XLSX或ODS导出的文档。The functions return NodeJS Buffer data that can be parsed using SheetJS.这些函数返回可以使用SheetJS解析的Node.jsBuffer数据。

Sample Code示例代码 (click to show)

SheetJS can read data from XLSX files and ODS files. SheetJS可以从XLSX文件和ODS文件中读取数据。This example prints the worksheet names and CSV exports of each sheet.本例打印每张工作表的工作表名称和CSV导出。

• XLSX
• ODS

const XLSX = require("xlsx");

/* Connect to Google Sheet */
const ID = "<google sheet id>";
const doc = await require("./common")(ID);

/* Get XLSX export */
const buf = await doc.downloadAsXLSX();

/* Parse with SheetJS */
const wb = XLSX.read(buf);

/* Loop over the worksheet names */
wb.SheetNames.forEach(name => {
  /* Print the name to the console */
  console.log(name);

  /* Get the corresponding worksheet object */
  const sheet = wb.Sheets[name];

  /* Print a CSV export of the worksheet */
  console.log(XLSX.utils.sheet_to_csv(sheet));
});

const XLSX = require("xlsx");

/* Connect to Google Sheet */
const ID = "<google sheet id>";
const doc = await require("./common")(ID);

/* Get XLSX export */
const buf = await doc.downloadAsODS();

/* Parse with SheetJS */
const wb = XLSX.read(buf);

/* Loop over the worksheet names */
wb.SheetNames.forEach(name => {
  /* Print the name to the console */
  console.log(name);

  /* Get the corresponding worksheet object */
  const sheet = wb.Sheets[name];

  /* Print a CSV export of the worksheet */
  console.log(XLSX.utils.sheet_to_csv(sheet));
});