I'm trying to get my data to return with the cell that its located in, currently I can get the data to pull back for the values in the range without the cell. Any help is really appreciated!

var rangeValues = worksheet.getRange(1, 1, 2, 2).getValues();
console.log(rangeValues);

Currently Returns - [ 'Becky', '$700' ], [ 'John', '$600' ]

Trying to get - [ 'A1', 'Becky', 'B1', '$700' ] [ 'A2', 'John', 'B2', '$600' ]

CodePudding user response：

I believe your goal is as follows.

• You want to retrieve [ [ 'A1', 'Becky', 'B1', '$700' ], [ 'A2', 'John', 'B2', '$600' ] ] from var rangeValues = worksheet.getRange(1, 1, 2, 2).getValues();.
  • rangeValues is [ [ 'Becky', '$700' ], [ 'John', '$600' ] ].

In order to achieve your goal, how about the following modification?

Modified script:

// Ref: https://stackoverflow.com/a/21231012
const columnToLetter = column => {
  let temp,
    letter = "";
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp   65)   letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
};

var worksheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set "worksheet".

var rangeValues = worksheet.getRange(1, 1, 2, 2).getValues();
var res = rangeValues.map((r, i) => r.flatMap((c, j) => [columnToLetter(j   1)   (i   1), c]));
console.log(res);

• When this script is run, when rangeValues is [ 'Becky', '$700' ], [ 'John', '$600' ], [ [ 'A1', 'Becky', 'B1', '$700' ], [ 'A2', 'John', 'B2', '$600' ] ] is obtained.

Reference:

• map()