The getSheetByName function efficiently retrieves a specific worksheet from a Google Sheet by name or index, utilizing caching to optimize performance. It handles both direct worksheet objects and spreadsheet IDs with worksheet names or indices as input.

Run example

npm run import -- "get worksheet by name"

get worksheet by name

var importer = require('../Core');
var getInfo = importer.import("get google sheet info");
var loadedDocs = {};

function getSheetByName(docId, page) {
    return typeof page === 'string'
        ? Promise.resolve(loadedDocs[docId] || getInfo(docId))
            .then(info => loadedDocs[docId] = info)
            .then(() => page
                ? loadedDocs[docId].sheets.filter(s => s.properties.title === page)[0]
                : loadedDocs[docId].sheets.filter(s => s.properties.index === 0)[0])
        : Promise.resolve(page)
}

module.exports = getSheetByName;

const { Importer } = require('../Core');

/**
 * Retrieves Google Sheet information using the provided ID.
 *
 * @param {string} docId - The ID of the Google Sheet document.
 * @returns {object} The retrieved document information.
 */
async function getGoogleSheetInfo(docId) {
    // TODO: Implement Google Sheets API integration or caching for improved performance
    // For now, assume this function is already implemented and returns the document info
    // Replace this comment with the actual implementation
    return { sheets: [], docId }; // Mock response for demonstration purposes
}

class GoogleSheetsManager {
    constructor(core) {
        this.core = core;
        this.loadedDocs = {};
    }

    /**
     * Retrieves a Google Sheet by its name or index.
     *
     * @param {string} docId - The ID of the Google Sheet document.
     * @param {string|string[]} [page] - The name or index of the sheet to retrieve (optional).
     * @returns {object} The retrieved sheet information.
     */
    async getSheetByName(docId, page) {
        if (this.loadedDocs[docId]) {
            return this.loadedDocs[docId];
        }

        const docInfo = await getGoogleSheetInfo(docId);
        this.loadedDocs[docId] = docInfo;

        if (typeof page ==='string') {
            return docInfo.sheets.find(s => s.properties.title === page);
        }

        return docInfo.sheets.find(s => s.properties.index === page);
    }
}

module.exports = (core) => new GoogleSheetsManager(core);

This code defines a function getSheetByName that retrieves a specific worksheet from a Google Sheet based on its name or index.

Here's a breakdown:

1. Caching: It uses a loadedDocs object to cache information about Google Sheets to avoid redundant API calls.

2. Input Handling: It handles two input types:

   • A docId (spreadsheet ID) and a page (worksheet name or index).
   • A page object representing the worksheet to retrieve directly.

3. Fetching Sheet Information: If a docId and page are provided, it first checks if the sheet information is already cached. If not, it fetches it using the getInfo function (imported from ../Core) and caches it.

4. Worksheet Retrieval:

   • If a page name is provided, it finds the worksheet with that name in the cached sheet information.
   • If a page index is provided, it finds the worksheet with the corresponding index.

5. Return Value: It returns the found worksheet object.