2.1.7 • Published 4 months ago

array-to-google-sheets v2.1.7

Weekly downloads
23
License
MIT License
Repository
github
Last release
4 months ago

Array To Google Sheets

NPM version build status Test coverage David deps

Update a 2 dimensional array into Google Sheets (Spreadsheets). You can also get back data in array or csv. The library is build with Google Sheets API v4.

Features

  • Tested with huge amount of data (10000 rows x 100 columns) and optimized memory usage.
  • Support generating formula
  • Get Spreadsheets data in array or csv format
  • Can handle multiple sheet
  • Can update single row and single
  • An experiment feature of converting the sheet as array object

Usage

import {ArrayToGoogleSheets, IUpdateOptions} from "array-to-google-sheets"; // typescript

async function simple() {
    const googleSheets = new ArrayToGoogleSheets({keyFilename: "serviceAccount.json"});
    const spreadsheet = await googleSheets.getSpreadsheet("19Ef3falKKiHAOo3Ps13tVC9M0BaG-NoYngAZggt8Jzk-NodnEAz5gt3iak");
    await spreadsheet.updateSheet("sheetName", [[1, 2, 3]]);
}

async function advance() {
    // https://www.npmjs.com/package/google-auth-library

    // service account json (either one)
    const keyFilename = "serviceAccount.json";
    // credentials for service account (either one)
    const credentials = {client_email: "", private_key: ""};

    const spreadsheetId = "";
    const googleSheets = new ArrayToGoogleSheets({keyFilename, credentials});
    const spreadsheet = await googleSheets.getSpreadsheet(spreadsheetId);
    const {spreadsheetUrl, properties} = spreadsheet;
    const {title, locale, timeZone, defaultFormat} = properties;

    // find and delete
    const sheetName = "sheetName";
    const sheet = await spreadsheet.findSheet(sheetName);
    if (sheet) {
        const result = await sheet.delete();
    }

    // get sheet again
    const newSheet = await spreadsheet.findOrCreateSheet(sheetName);
    const url = newSheet.getUrl();

    //  update
    const values1 = [
        [1, 2, 3],
        [1.1, 2.2, -3.33],
        ["abc", "cde", "xyz"],
    ];
    const updateOptions: IUpdateOptions = {
        minRow: 3, // styling
        minColumn: 3, // styling
        margin: 2,  // styling
        fitToSize: true,  // remove empty cells
        clearAllValues: true, // clear all existing values
    };
    const updateResult1 = await newSheet.update(values1, updateOptions);
    const resultValues1 = await newSheet.getValues();

    // export into csv
    await newSheet.exportAsCsv("data.csv");
}

async function updateRowsAndCells() {
    // expand the sheet size first if u have many rows
    const googleSheets = new ArrayToGoogleSheets({keyFilename: "serviceAccount.json"});
    const spreadsheet = await googleSheets.getSpreadsheet("spreadsheetId");
    const sheet = await spreadsheet.findOrCreateSheet("sheetName");

    // we have to make sure we have enough grids
    await sheet.resize(10, 10);

    for (let i = 0; i < 10; i++) {
        await sheet.updateRow(i, [1, 2, 3]);
        await sheet.updateCell(i, i, 1);
    }
}

spreadsheetId

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

KeyFilename / Service Account

  • Create a Google Cloud Project
  • Create Service Account
    • Service account details > Choose any service account name > CREATE
    • Grant this service account access to project > CONTINUE
    • Grant users access to this service account ( > CREATE KEY
    • Save the key file into your project
  • Enable Drive API & Google Sheets API
    • APIs and Services > Enable APIS AND SERVICES
    • Search Google Drive API > Enable
    • Search Google Sheets API > Enable
  • Enable Google Sheets API
  • Open the JSON key file, you will find an email xxx@xxx.iam.gserviceaccount.com.
  • Go to your Google Spreadsheets and shared the edit permission to the email address.

Formula Example

let values = [
    [{formula: '=sum(%1:%2)', cells: [{row: 1, col: 1}, {row: 1, col: 3}]}], 
    // =sum(A1:C1)
    [{formula: '=%1/50', cells: [{row: 1, col: 3}]}], 
    // =C1/50
    [{formula: '=sum(%1:%2)', cells: [{row: 'this', col: 1}, {row: 'this', col: 3}]}], 
    // =sum(A3:C3)
    [{formula: '=sum(%1:%2)', cells: [{row: 1, col: 'this'}, {row: 3, col: 'this'}]}],
     // =sum(A1:A3);
    [{formula: '=sum(%1:%2)', cells: [{row: 1, col: 0}, {row: 1, col: 0}]}], 
    // =sum(1:1);
    [{formula: '=sum(%1:%2)', cells: [{row: 1}, {row: 1}]}], 
    // =sum(1:1);
    [{formula: '=sum(%1:%2)', cells: [{row: 0, col: 2}, {row: 0, col: 2}]}] 
    // =sum(B:B);
];

Experiment Object Sheet Feature

async function experimentalObjectSheet() {
    const googleSheets = new ArrayToGoogleSheets({keyFilename: "serviceAccount.json"});
    const spreadsheet = await googleSheets.getSpreadsheet("spreadsheetId");
    const sheet = await spreadsheet.findOrCreateSheet("sheetName");

    const values = [
        ["value1", "value2/string", "value3/number", "value4/boolean", "value5/date", "value6/number[]", "value7/string[]", "value8/ignore"],
        ["1", "2", "3", "4", "5", "6", "7", "8", "9"],
        [1, 2, 3, 4, 5, 6, 7, 8, 9],
        ["a", "b", "c", "d", "e", "f", "g", "h", "i"],
    ];
    await sheet.update(values, {clearAllValues: true, margin: 2});

    type IObject = {value1: string; value2: string; value3: number; value4: boolean; value5: Date; value6: number[]; value7: string[]};
    const objectSheet = await sheet.exportAsObjectSheet<IObject>();
    const type = objectSheet.getType();

    // this can print the type IObject
    console.log("typescript", type);

    // get data as object
    for (let i = 0; i < objectSheet.length; i++) {
        const item = objectSheet.get(i);
        item.value1 = "key" + i;
        item.value2 = "value" + i;
        item.value3 = Math.random();
        item.value4 = true;
        item.value5 = new Date();
        item.value6 = [i, 1, 2, Math.random()];
        item.value7 = [i.toString(), "a", "b", "c"];
        await item.save();
    }
}
value1value2/stringvalue3/numbervalue4/booleanvalue5/datevalue6/number[]value7/string[]value8/ignore
key0value00.7238840059TRUE2020-03-03T05:41:02.926Z0, 1, 2, 0.8650, a, b, c8
key1value10.2963643265FALSE2020-03-03T05:41:03.149Z1, 1, 2, 0.9951, a, b, c8

The above table will be converted as:

async function sheetObject() {
    interface IObject {value1: string; value2: string; value3: number; value4: boolean; value5: Date; value6: number[]; value7: string[];}
    const objectSheet = await sheet.exportAsObjectSheet<IObject>();
    // generate the above IObject 
    const objectInterface = objectSheet.getInterface();
    const {headers, size, rawValues, rawHeaders} = objectSheet;
    const firstItem = objectSheet.get(0);

    // iterator
    for (const item of objectSheet) {
        console.log(item.toObject());
        item.value1 = "new Value";
        // this will only update the changed cell values to minimize modifing the original values as much as possible
        await item.save();
    }

   const findItem = objectSheet.toArray().find(x => x.value1 === "key");
    const objects = objectSheet.toObjects();
    
    // add new item
    const newItem = await objectSheet.append({} as any);
    // you have to manage the sheet size yourself in case of error
    await sheet.resize(100, 100);
}
/* 
[
  {
    value1: 'key0',
    value2: 'value0',
    value3: 0.7238840059,
    value4: true,
    value5: 2020-03-03T05:41:02.926Z
    value6: [ 0, 1, 2, 0.865 ],
    value7: [ '0', 'a', 'b', 'c' ]
  }
  {
    value1: 'key1',
    value2: 'value1',
    value3: 0.2963643265,
    value4: false,
    value5: 2020-03-03T05:41:03.149Z
    value6: [ 1, 1, 2, 0.995 ],
    value7: [ '1', 'a', 'b', 'c' ]
  }
]
*/

Links

2.1.7

4 months ago

2.1.6

10 months ago

2.1.5

1 year ago

2.1.4

1 year ago

2.1.3

1 year ago

2.1.2

1 year ago

2.1.1

1 year ago

2.1.0

1 year ago

2.0.1

1 year ago

2.0.0

1 year ago

1.2.0

1 year ago

1.1.7

1 year ago

1.1.6

1 year ago

1.1.5

1 year ago

1.1.4

1 year ago

1.1.3

1 year ago

1.1.2

1 year ago

1.1.1

2 years ago

1.1.0

2 years ago

1.0.1

3 years ago

1.0.0

3 years ago

0.2.1

3 years ago

0.2.0

3 years ago

0.1.0

3 years ago