Giter Club home page Giter Club logo

sheets-simplified's Introduction

Sheets Simplified

TypeScript classes based package that eases and increases safety of working with Google Sheets API v4.

Usage recommendation

Auth and SheetsConnection setup

Create a google-auth-wrapper.ts file that contains your Google Cloud login info.

import { GoogleSheetsAuth } from 'sheets-simplified';

const googleAuthWrapper = new GoogleSheetsAuth({
    email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
    key: process.env.GOOGLE_SERVICE_SECRET_KEY,
}).login();

export { googleAuthWrapper };

Create a SheetsConnection object. The minimal required info to connect to a spreadsheet is the auth and spreadsheetId. You can also provide more info at this point, like sheet, range, or even more specific options like valueRenderOption. List of available options can be found in this file.

const sheetsConnection = new SheetsConnection({
    auth: googleAuthWrapper,
    spreadsheetId: process.env.GOOGLE_SPREADSHEET_ID,
    sheet: "Sheet1",
    range: "A1:D12",
});

Main methods

Get data

To retrieve data from sheet you can simply use get method.

const data = await sheetsConnection.get();

If you haven't provided sheet and range in the SheetsConnection constructor or if you want to use different values, you can provide them directly within the get method.

const data = await sheetsConnection.get({
    sheet: "Sheet1",
    range: "A1:D4",
});

You can also provide additional options like majorDimension, valueRenderOption, or dateTimeRenderOption in the get method to customize the data retrieval.

const data = await sheetsConnection.get({
    majorDimension: "COLUMNS",
    valueRenderOption: "UNFORMATTED_VALUE",
    dateTimeRenderOption: "FORMATTED_STRING",
});

Append data

To append data to a sheet, you can use the append method. Provide an array of data you want to add to the sheet as the first argument. Each inner array represents a row in the sheet.

const response = await sheetsConnection.append([
    ["A4", "B4", "C4", "D4"]
    ["A5", "B5", "C5", "D5"]
]);

Inserted will look like this:

A1B1C1D1
A2B2C2D2
A3B3C3D3
A4B4C4D4
A5B5C5D5

You can also provide a special config object as the second argument to the append method, allowing you to specify various options:

const response = await sheetsConnection.append([
    ["A1", "B1", "C1", "D1"],
    ["A2", "B2", "C2", "D2"],
    ["A3", "B3", "C3", "D3"],
    ["A4", "B4", "C4", "D4"],
], {
    valueInputOption: "USER_ENTERED",
    insertDataOption: "INSERT_ROWS",
    includeValuesInResponse: true,
    responseDateTimeRenderOption: "FORMATTED_STRING",
    responseValueRenderOption: "FORMATTED_VALUE",
});

Update data

To update data in sheets, you can use the update method. Provide an array of data you want to update in the specified sheet range as the first argument.

const response = await sheetsConnection.update(
    [
        ["E2", "F2", "G2", "H2"],
        ["E3", "F3", "G3", "H3"],
    ],
    {
        sheet: "Sheet1",
        range: "A2:D3",
    }
);

Updated data will look like this:

A1B1C1D1
E2F2G2H2
E3F3G3H3
A4B4C4D4
A5B5C5D5

Clear data

To clear data in sheets, you can use the clear method. If you've already provided sheet and range in the constructor, you don't need to provide any additional arguments. However, if you want to use different values, you can specify them in the first parameter by creating a configuration object with the sheet and range properties.

const response = await sheetsConnection.clear({
    sheet: "Sheet1",
    range: "A2:D3",
});

Cleared data will look like this:

A1B1C1D1
A4B4C4D4
A5B5C5D5

Create new sheet

To create a new sheet, you can use the createSheet method. Simply provide the desired sheetName in the configuration object.

const response = await sheetsConnection.createSheet({
    sheetName: "New Sheet",
});

If you want to change sheet provided in the constructor you can provide allowSheetNameModifications in configuration object or in constructor (this is set to true as default).

const response = await sheetsConnection.createSheet({
    sheetName: "New Sheet",
    allowSheetNameModifications: true,
});

Delete sheet

To delete a sheet, you can use the deleteSheet method. Provide either the sheetName or sheetId in the configuration object. If neither is provided, the constructor's sheet value will be used (if it's set).

If you want to change sheet provided in the constructor you can provide allowSheetNameModifications in configuration object or in constructor (this is set to true as default).

With sheet name:

const response = await sheetsConnection.deleteSheet({
    sheetName: "New Sheet",
});

With sheet ID:

const response = await sheetsConnection.deleteSheet({
    sheetId: 12345678,
});

Create Named Range

To create a named range, use the createNamedRange method, and provide the name, range and neither sheetName or sheetId in the configuration object. Currently, the package supports ranges with one letter column names, for example, "A1:B40" works, but "A1:AB40" won't work. However, this limitation is planned to be changed in the future. For ranges that exceed one letter, you can use Google's default way by providing startRowIndex, endRowIndex, startColumnIndex, and endColumnIndex in the configuration object.

With range:

const response = await sheetsConnection.createNamedRange({
    name: "NewNamedRange",
    sheetName: "Sheet1",
    range: "A1:D4",
});

With startRowIndex, endRowIndex, startColumnIndex and endColumnIndex:

const response = await sheetsConnection.createNamedRange({
    name: "NewNamedRange",
    sheetId: 93726320,
    startRowIndex: 0,
    endRowIndex: 3,
    startColumnIndex: 0,
    endColumnIndex: 3,
});

Delete named range

To delete a named range, use the deleteNamedRange method, and provide either the name or namedRangeId in the configuration object.

With name:

const response = await sheetsConnection.deleteNamedRange({
    name: "NewNamedRange",
});

With namedRangeId:

const response = await sheetsConnection.deleteNamedRange({
    namedRangeId: "a1b2c3d4",
});

Special Features

When retrieving data, you can set firstRowAsHeader to true to format the data as an object with keys derived from the first row. You can enable this feature either in the constructor or in the get method.

Example of a normal response:

[
    ["A1", "B1", "C1", "D1"],
    ["A2", "B2", "C2", "D2"],
    ["A3", "B3", "C3", "D3"],
]

Response with firstRowAsHeader set to true:

[
    {
        A1: "A2",
        B1: "B2",
        C1: "C2",
        D1: "D2",
    },
    {
        A1: "A3",
        B1: "B3",
        C1: "C3",
        D1: "D3",
    },
]

Development setup

Install dependencies

npm install

Build

npm run build

Run tests

npm run test

Compiled JavaScript will be placed in /build folder.

Made by Michał Szajner

sheets-simplified's People

Contributors

m1chals avatar

Stargazers

 avatar  avatar  avatar  avatar

Watchers

 avatar

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.