This code defines a function getRows that retrieves data from a specified worksheet in a Google Sheet, handling authentication and API interactions to return the data as an array of rows.

Run example

npm run import -- "get worksheet rows"

get worksheet rows

var importer = require('../Core');
var authorizeSheets = importer.import("authorize sheets api");
var getInfo = importer.import("get google sheet info");
var getSheetByName = importer.import("get worksheet by name");

function getRows(link, worksheet) {
    var sheets;
    if(typeof link === 'object') (worksheet = link, link = worksheet.spreadsheetId);
    return authorizeSheets()
        .then(s => (sheets = s, getSheetByName(link, worksheet)))
        .then(ws => (worksheet = ws,
              console.log(`reading sheet: ${worksheet.properties.title}`
                          + ` - ${worksheet.properties.gridProperties.rowCount}`)))
        .then(() => sheets.spreadsheets.values.get({
            spreadsheetId: worksheet.spreadsheetId,
            range: `'${worksheet.properties.title}'!A1:Z${worksheet.properties.gridProperties.rowCount}`
        }))
        .then(res => res.data.values || [])
}

module.exports = getRows;

// Import the necessary modules
const { authorizeSheets, getSheetByName } = require('../Core');

/**
 * Retrieves rows from a Google Sheet based on the provided link and worksheet name.
 *
 * @param {object|string} link - The ID or object containing the ID and worksheet name.
 * @param {string} [worksheet] - The name of the worksheet. If not provided, it will be extracted from the link object.
 * @returns {Promise>>} A promise that resolves to an array of rows in the worksheet.
 */
function getRows(link, worksheet) {
    // Ensure link is an object and worksheet is a string
    if (typeof link === 'object') {
        if (!link.spreadsheetId) {
            throw new Error('Invalid object. Expected object with spreadsheetId property.');
        }
        (worksheet = link.name, link = link.spreadsheetId);
    }

    // Authorize the sheets API
    return authorizeSheets()
       .then(sheets => {
            // Get the worksheet by its ID or name
            return getSheetByName(link, worksheet);
        })
       .then(worksheet => {
            // Extract the worksheet ID and title
            const { spreadsheetId, properties } = worksheet;
            console.log(`Reading sheet: ${properties.title} - ${properties.gridProperties.rowCount}`);

            // Get the rows from the worksheet
            return sheets.spreadsheets.values.get({
                spreadsheetId,
                range: `'${worksheet.properties.title}'!A1:Z${worksheet.properties.gridProperties.rowCount}`,
            });
        })
       .then(res => res.data.values || []);
}

module.exports = getRows;

This code snippet defines a function getRows that retrieves data from a specified worksheet in a Google Sheet.

Here's a breakdown:

1. Imports:

   • Imports functions for authorizing Google Sheets API access, getting sheet information, and retrieving a worksheet by name.

2. getRows Function:

   • Takes a link (either a spreadsheet ID or an object containing the spreadsheet ID and worksheet name) and an optional worksheet name as arguments.
   • If link is an object, it extracts the spreadsheet ID and worksheet name.
   • Uses authorizeSheets to obtain an authorized Google Sheets client.
   • Calls getSheetByName to retrieve the specified worksheet using the spreadsheet ID and worksheet name.
   • Logs a message indicating the sheet being read.
   • Uses the authorized client to make a request to the Google Sheets API to retrieve data from the specified worksheet range (column A to Z, from row 1 to the last row).
   • Returns the retrieved data as an array of rows.

Purpose:

This function provides a way to fetch data from a specific worksheet in a Google Sheet, handling authentication and API interaction.