Object-to-google-spreadsheet NPM

Transform and publish your JSON objects into a Google spreadsheet.

Installation

Example usage

const O2GS = require('object-to-google-spreadsheet');
// or use 'import' to get it with types in Typescript

// load your Google service account
const creds = require('./creds');

// get your doc key from the spreadsheet's URL
const docKey = 'Y7usmqsic4djsaxXWqaaS';

const myReport = new O2GS(creds, docKey);

// The array of objects which will build the spreadsheet
const docs = [
  {
    person : "John",
    properties : { Age: 25,  Address : "16 main st." }
  },
  {
    person : "Jane",
    properties : { Age : 24, Hobbies : ["swimming", "Javascripting"]}
  }
];

// optional
const options = {
    sheetName: 'My Awesome Report',
    rowName: 'person',
    properties: 'properties',
    a1Field: 'details', 
    sort: true, 
    removeBase: false 
};

(async ()=> {
  try {
    // populate the sheet
    await myReport.push(docs, options);
  } catch(err) {
    console.log(err);
  }
})();

Example result

updated sheet

docKey

Every Google Sheet has a unique key in the URL
https://docs.google.com/spreadsheets/d/{docKey}/

Creds

Go to the Google Developers Console
Select or Create Project
Dashboard > Enable APIs and Services > Enable the Drive API for your project
Credentials > Create Service Account Key
Select Json Key type and save the downloaded json file to your project
Once you have created the services account, you will have an email xxx@xxx.iam.gserviceaccount.com. Go to your Google Sheets file and shared the edit permission to the email address.
For more details, please refer to https://www.npmjs.com/package/google-spreadsheet