node.js - How to export JavaScript object to Google Sheet using NodeJs - Stack Overflow

admin管理员组

文章数量:1320808

I have a JavaScript object like this

[{ a: "123", b: "456" }, { a: "321", b: "654" }]

Is there any way to export it to Google Sheet like this using NodeJs

I have a JavaScript object like this

[{ a: "123", b: "456" }, { a: "321", b: "654" }]

Is there any way to export it to Google Sheet like this using NodeJs

• javascript
• node.js
• google-sheets
• google-api
• google-sheets-api

Share Improve this question Follow asked May 15, 2022 at 17:06 BlackLotusBlackLotus 45066 silver badges1313 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 11

I believe your goal is as follows.

• You want to put the values of [{ a: "123", b: "456" }, { a: "321", b: "654" }] to the Spreadsheet using Node.js.

In this case, how about using google-spreadsheet?

Usage:

1. Install google-spreadsheet.

You can see it at here.

2. Authorization

I thought that in this case, the service account might be useful.

You can see the method for authorization at here.

3. Sample script:

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

const creds = require("credential.json"); // Please set your credential file.

const doc = new GoogleSpreadsheet("##"); // Please set your Spreadsheet ID.

const sample = async () => {
  await doc.useServiceAccountAuth(creds);
  await doc.loadInfo();
  const worksheet = doc.sheetsByIndex[0]; // Here, 1st tab on Google Spreadsheet is used.

  // This is from your sample value.
  const values = [
    { a: "123", b: "456" },
    { a: "321", b: "654" },
  ];
  await worksheet.setHeaderRow(["a", "b"]); // This is the header row.
  await worksheet.addRows(values); // Your value is put to the sheet.
};

sample();

• When this script is run, the result in your sample image is obtained.

References:

• google-spreadsheet
• document of google-spreadsheet

You could convert your JSON content to CSV and just use the Google Sheets import.

1. Convert your JSON file / data to CSV using a npm package: https://www.npmjs./package/json2csv How can I convert JSON to CSV?
2. Create a CSV file with it (you can console.log your content and copy it or just use fs to create a file)
3. Import the csv file by using the UI:

You could also use an online tool for it: https://www.convertcsv./json-to-csv.htm There you just have to copy your json and can even download a "Excel"-file with it. (This file can get imported like CSV as well)

Another way is to use a Google Sheets plugin. However I was only able to find tools for loading JSON via an URL / API. E.g.: https://medium./@paulgambill/how-to-import-json-data-into-google-spreadsheets-in-less-than-5-minutes-a3fede1a014a

本文标签： nodejsHow to export JavaScript object to Google Sheet using NodeJsStack Overflow