Google Sheets API Wrapper

@mernjs/googlesheets is a Node.js module that provides a simple way to interact with Google Sheets using the @mernjs/googlesheets. It allows for authorization, sheet management, and CRUD operations on data within a Google Spreadsheet.

Table of Contents

• 1. Features
• 2. Installation
• 3. Usage
  • 3.1 Initialization
  • 3.2 Authorization
  • 3.3 Connect to a Sheet
  • 3.4 Sheet Management
    • 3.4.1 Create Sheet
    • 3.4.2 Get All Sheets
    • 3.4.3 Get Sheet
    • 3.4.4 Update Sheet
    • 3.4.5 Delete Sheet
  • 3.5 Data Manipulation
    • 3.5.1 Add Header
    • 3.5.2 Insert Data
    • 3.5.3 Find Data
    • 3.5.4 Update Data
    • 3.5.5 Delete Data
• 4. Full Code Example
• 5. Error Handling
• 6. Download Service JSON
• 7. Contributing

1. Features

• Authorization: Authenticate using a service account key file.
• Sheet Management: Create, update, and delete sheets within a spreadsheet.
• Data Operations: Add headers, insert data, and manage records with filtering and pagination.

2. Installation

Install the required packages using npm:

npm install @mernjs/googlesheets

3. Usage

Here's how to use the GoogleSheets class:

3.1 Initialization

This method initializes a new instance of the GoogleSheets class, allowing you to perform operations on Google Sheets.

• Method:

  • constructor()

• Parameters: N/A

• Returns:

  • A new instance of the GoogleSheets class.

• Sample Code:

const sheets = new GoogleSheets();

3.2 Authorization

This method authenticates the application with the Google Sheets API using a service account. It establishes the necessary credentials to perform operations on the specified spreadsheet.

• Method:

  • authorize(keyFile, spreadsheetId)

• Parameters:

  • keyFile: (string) Path to the service account key file in JSON format.
  • spreadsheetId: (string) The unique ID of the spreadsheet you wish to interact with.

• Returns:

  • A Promise that resolves when authorization is successful.

• Sample Code:

await sheets.authorize('path/to/keyfile.json', 'your-spreadsheet-id');

3.3 Connect to a Sheet

This method sets the name of the active sheet you wish to interact with for subsequent operations.

• Method:

  • connectSheet(sheetName)

• Parameters:

  • sheetName: (string) The name of the sheet you want to connect to.

• Returns:

  • A Promise that resolves when the sheet is connected.

• Sample Code:

await sheets.connectSheet('MyActiveSheet');
console.log('Connected to sheet:', sheets.sheetName);

3.4 Sheet Management

This section includes methods for managing sheets in your Google Spreadsheet. You can create, retrieve, update, and delete sheets, allowing for flexible organization of your data.

3.4.1 Create Sheet

This method creates a new sheet within the authorized spreadsheet.

• Method:

  • createSheet(sheetTitle)

• Parameters:

  • sheetTitle: (string) The title for the new sheet.

• Returns:

  • A Promise that resolves with an object containing the ID and name of the newly created sheet.

• Sample Code:

const newSheet = await sheets.createSheet('NewSheetTitle');
console.log('Created sheet:', newSheet);

3.4.2 Get All Sheets

This method retrieves an array of all sheets in the authorized spreadsheet.

• Method:

  • getAllSheets()

• Parameters: N/A

• Returns:

  • A Promise that resolves with an array of objects, each containing the ID and name of a sheet.

• Sample Code:

const allSheets = await sheets.getAllSheets();
console.log('All sheets:', allSheets);

3.4.3 Get Sheet

This method retrieves all data values from a specified sheet identified by its ID.

• Method:

  • getSheet(sheetId)

• Parameters:

  • sheetId: (string) The ID of the sheet from which to retrieve data.

• Returns:

  • A Promise that resolves with an array of values from the specified sheet.

• Sample Code:

const data = await sheets.getSheet('sheetId123');
console.log('Sheet data:', data);

3.4.4 Update Sheet

This method updates the title of a specified sheet.

• Method:

  • updateSheet(sheetId, newTitle)

• Parameters:

  • sheetId: (string) The ID of the sheet you want to update.
  • newTitle: (string) The new title for the specified sheet.

• Returns:

  • A Promise that resolves with an object containing the updated sheet's ID and title.

• Sample Code:

const updatedSheet = await sheets.updateSheet('sheetId123', 'UpdatedSheetTitle');
console.log('Updated sheet:', updatedSheet);

3.4.5 Delete Sheet

This method deletes a specified sheet from the authorized spreadsheet.

• Method:

  • deleteSheet(sheetId)

• Parameters:

  • sheetId: (string) The ID of the sheet you want to delete.

• Returns:

  • A Promise that resolves with a confirmation message of the deletion.

• Sample Code:

const confirmation = await sheets.deleteSheet('sheetId123');
console.log('Deleted sheet:', confirmation);

3.5 Data Manipulation

This section provides methods for handling data within sheets. You can add headers, insert records, search for data, and update or delete existing records, facilitating efficient data management.

3.5.1 Add Header

This method adds headers to the specified active sheet.

• Method:

  • addHeader(headers)

• Parameters:

  • headers: (array of strings) An array of header names to be added to the top row of the sheet.

• Returns:

  • A Promise that resolves when the headers are successfully added.

• Sample Code:

await sheets.addHeader(['Header1', 'Header2', 'Header3']);
console.log('Headers added successfully.');

3.5.2 Insert Data

This method inserts a new record into the active sheet.

• Method:

  • insert(data)

• Parameters:

  • data: (object) An object where keys correspond to header names, and values are the data to be inserted.

• Returns:

  • A Promise that resolves when the data is appended.

• Sample Code:

await sheets.insert({ Header1: 'Data1', Header2: 'Data2', Header3: 'Data3' });
console.log('Data inserted successfully.');

3.5.3 Find Data

This method retrieves records based on filter criteria and optional pagination.

• Method:

  • find(filterObj = {}, paginationObj = {})

• Parameters:

  • filterObj: (object) An object with key-value pairs for filtering records.
  • paginationObj: (object, optional) Contains pagination parameters.

• Returns:

  • A Promise that resolves with an object containing the filtered data and pagination information.

• Sample Code:

const results = await sheets.find({ Header1: 'Data1' }, { page: 1, pageSize: 10 });
console.log('Filtered records:', results);

3.5.4 Update Data

This method updates a single record that matches the specified criteria.

• Method:

  • updateOne(criteria, updates)

• Parameters:

  • criteria: (object) An object to identify the record to update.
  • updates: (object) An object with key-value pairs for the updates.

• Returns:

  • A Promise that resolves when the record is updated.

• Sample Code:

await sheets.updateOne({ Header1: 'Data1' }, { Header2: 'UpdatedData2' });
console.log('Record updated successfully.');

3.5.5 Delete Data

This method deletes a single record that matches the specified criteria.

• Method:

  • deleteOne(criteria)

• Parameters:

  • criteria: (object) An object to identify the record to delete.

• Returns:

  • A Promise that resolves when the record is deleted.

• **

Sample Code**:

await sheets.deleteOne({ Header1: 'Data1' });
console.log('Record deleted successfully.');

4. Full Code Example

Here’s a complete example demonstrating how to use the GoogleSheets class to perform various operations:

import { GoogleSheets } from '@mernjs/googlesheets';

async function main() {
	const sheets = new GoogleSheets();

	// Authorize with your service account key and spreadsheet ID
	await sheets.authorize('path/to/google-service.json', 'your-spreadsheet-id');
	console.log('Authorized');

	// Create a new sheet
	const newSheet = await sheets.createSheet('NewSheetTitle');
	console.log('Created sheet:', newSheet);

	// Get all sheets
	const allSheets = await sheets.getAllSheets();
	console.log('All sheets:', allSheets);

	// Connect to the new sheet
	await sheets.connectSheet('NewSheetTitle');
	console.log('Sheet Connected');

	// Add headers
	await sheets.addHeader(['Header1', 'Header2', 'Header3']);
	console.log('Header Created');

	// Insert data
	await sheets.insert({ Header1: 'Data1', Header2: 'Data2', Header3: 'Data3' });
	console.log('Data Inserted');

	// Find data
	const results = await sheets.find({ Header1: 'Data1' });
	console.log('Found records:', results);

	// Update a specific record
	await sheets.updateOne({ Header1: 'Data1' }, { Header2: 'UpdatedData2' });
	console.log('Data Updated');

	// Delete a record
	await sheets.deleteOne({ Header1: 'Data1' });
	console.log('Data Deleted');
}

main().catch(console.error);

5. Error Handling

Each method throws an error if the authorization is incomplete or if the provided parameters are invalid. Catch errors in your application as needed.

6. Download Service JSON:

1. Create a New Project

• Go to Google Cloud Console.
• Click on the project dropdown at the top and select New Project.
• Enter a project name (e.g., My Google Sheets App) and click Create.

2. Enable the Google Sheets API

• Navigate to APIs & Services → Library.
• In the search bar, type Google Sheets API.
• Click on Google Sheets API and then click Enable.

3. Create a Service Account

• Go to IAM & Admin → Service Accounts.
• Click on Create Service Account.
• Provide a name (e.g., sheets-api-service-account).
• Click Create.
• Grant the role Editor or Owner to the service account.
• Click Continue and then Done.

4. Create and Download Service Account Key

• After creating the service account, go to the Keys section.
• Click Add Key → Create New Key.
• Choose JSON and click Create.
• A .json file containing your service account credentials will be downloaded. Save this file securely.

5. Share Your Google Sheet with the Service Account

• Open the Google Sheet you want to access.
• Click on the Share button in the top right corner.
• Enter the service account email address (it looks like [email protected]).
• Grant the service account Editor access.
• Click Send.

6. Access the Google Sheets API Using the Service Account

• Use the downloaded .json credentials in your application to authenticate and access the Google Sheets API.
• Make sure to include @mernjs/googlesheets to handle authentication and Google Sheets API requests.

7. Contributing

If you find any issues or have suggestions for improvement, feel free to open an issue or submit a pull request on GitHub.