Get array of joined values from Google sheets-CodePudding

In a Google sheet I have objects (rows) which are unambiguously identifiable by combination of two of their values id and date stored in non adjacent columns. Now I need to check if a certain object of this type exist within this sheet and if so in which row. I'd like to have an array like object[<index = row no>][<id> <delimiter> <date>] that allows me to find and locate one of them by this:

sheet = SpreadsheetApp.openById('1234567890').getSheetByName('Entries List');
objectsIds = sheet.getRange(1, 5, 100).getDisplayValues();    // with 5 = column containing ids
objectsDates = sheet.getRange(1, 11, 100).getDisplayValues(); // with 11 = column containing dates

// Create array of objects with joint/combined value of id # date:
//                                 <-------ID---------->#<--date-->
consloe.log(objects[0]); // writes e. g. "4e6aa8-8f709d-a208b38#2022-02-19" which is unambiguously identifiable

sampleIdDate = sampleId   "#"   sampleDate;
rowOfFirstOccurance = objects.findIndex(e => === sampleIdDate);

Actually the delimiter can be omitted. What is the best way to create this array of combined values in one attribute/field?

CodePudding user response：

In your script, how about the following modification?

Modified script:

var sheet = SpreadsheetApp.openById('1234567890').getSheetByName('Entries List');
var objectsIds = sheet.getRange(1, 5, 100).getDisplayValues();    // with 5 = column containing ids
var objectsDates = sheet.getRange(1, 11, 100).getDisplayValues(); // with 11 = column containing dates

var objects = objectsIds.map(([v], i) => `${v}#${objectsDates[i][0]}`); // Added

// Create array of objects with joint/combined value of id # date:
//                                 <-------ID---------->#<--date-->
console.log(objects[0]); // writes e. g. "4e6aa8-8f709d-a208b38#2022-02-19" which is unambiguously identifiable
var sampleIdDate = sampleId   "#"   sampleDate;
var rowOfFirstOccurance = objects.findIndex(e => e === sampleIdDate); // Modified

In your script, please modify consloe.log(objects[0]); to console.log(objects[0]);.
From Actually the delimiter can be omitted., if you want to remove the delimiter, please modify var objects = objectsIds.map(([v], i) => ${v}#${objectsDates[i][0]}); to var objects = objectsIds.map(([v], i) => ${v}${objectsDates[i][0]});.

Note:

As additional information, for example, in your script, you are using 2 getDisplayValues() methods. I think that this can be reduced to one time as follows. By this, the process cost can be reduced a little.

From

  var sheet = SpreadsheetApp.openById('1234567890').getSheetByName('Entries List');
  var objectsIds = sheet.getRange(1, 5, 100).getDisplayValues();    // with 5 = column containing ids
  var objectsDates = sheet.getRange(1, 11, 100).getDisplayValues(); // with 11 = column containing dates

  var objects = objectsIds.map(([v], i) => `${v}#${objectsDates[i][0]}`); // Added

  var sheet = SpreadsheetApp.openById('1234567890').getSheetByName('Entries List');
  var values = sheet.getRange(1, 5, 100, 7).getDisplayValues();
  var objects = values.map(r => `${r[0]}#${r[6]}`);

Reference:

map()