Use Cell Range instead of A1-notation in Google Sheets

Question

I am using the Google API to fetch data from a Google Spreadsheet via Node.js. In Excel VBA it is possible to either use

Range("A3") or Cells(3,1)

to reference the range of the cell.

And for a multiple cell range it would be like:

Range(Cells(1,1), Cells(2,3))

instead of:

Range("A1:C2")

I could not find out how to do that via the Google Sheets API.

Tried following this guideline, but I think it's not the thing I need here: https://googlesheets4.tidyverse.org/articles/range-specification.html

My example code in Node.js looks like this:

async function getHelperData(cl){
    const gsapi = google.sheets({version:'v4', auth: cl});
    const opt = {
        spreadsheetId: gs.gs_ID,
        range: '_helperSheet!A1:C2'
    };

    let data = await gsapi.spreadsheets.values.get(opt);
    let dataArray = data.data.values;
    return dataArray;
};

Is it actually possible?

score 1 · Answer 1 · answered Oct 28 '22 at 11:29

About Use Cell Range instead of A1-notation in Google Sheets and the use of a value like Range(Cells(1,1), Cells(2,3)), I thought that in the current stage, when Sheets API is used, the a1Notation is used for retrieving values from Google Spreadsheet. So, in this case, I thought that it is required to convert a value like Range(Cells(1,1), Cells(2,3)) to the a1Notation.

When this is reflected in your script, how about the following modification?

Modified script:

const gsapi = google.sheets({version:'v4', auth: cl});

const sheetName = "_helperSheet"; // Please set sheet name.
const rangeObj = { start: [1, 1], end: [2, 3] }; // This is from `Range(Cells(1,1), Cells(2,3))` of "A1:C2" in your question.

const columnIndexToLetter_ = (index) => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26)) : ""; // Ref: https://stackoverflow.com/a/53678158

const a1Notation = rangeObj.end ? `'${sheetName}'!${columnIndexToLetter_(rangeObj.start[1] - 1)}${rangeObj.start[0]}:${columnIndexToLetter_(rangeObj.end[1] - 1)}${rangeObj.end[0]}` : `'${sheetName}'!${columnIndexToLetter_(rangeObj.start[1] - 1)}${rangeObj.start[0]}`;
const opt = {
  spreadsheetId: gs.gs_ID,
  range: a1Notation,
};

let data = await gsapi.spreadsheets.values.get(opt);
let dataArray = data.data.values;
console.log(dataArray);

In this modification, it supposes that the value of gs.gs_ID has already been declared elsewhere. Please be careful about this.
In this modification, a1Notation returns '_helperSheet'!A1:C2.
If const rangeObj = { start: [1, 1] }; is used, a1Notation returns '_helperSheet'!A1.

Use Cell Range instead of A1-notation in Google Sheets

1 Answers1

Modified script: