How to modify an array of array, depending if an element in it matches an element from another array of array?

user_14430403 0
Viewed 75

I have 2 set of array of arrays in Google Spreadsheet as follows:-

var arrayInput = [[ASIAPLY, "9/10/2020"], [PCCS, "9/10/2020"], [SCGM, "9/10/2020"]]

var arrayOuput = [[PCCS, "8/10/2020"]]

I want to insert the 2nd index of an element/array in the arrayInput if the 1st index is present in the outputArray. If it is not, I would like to add the whole element/array into outputArray. My desired outcome would look like this

var arrayOuput = [[PCCS, "9/10/2020", "8/10/2020"], [ASIAPLY, "9/10/2020"], [SCGM, "9/10/2020"]]

I have tried this

function testData() {
  
  // get the range of input data
  var arrayInput = wlSheet.getRange(2, 2, 3, 2).getValues(); 
  
  // get the range of output counter
  var arrayOuput = wlSheet.getRange(2, 7, 1, 2).getValues();
  
  arrayOuput.find((outputRow, i, arr) => {
  
    arrayInput.map((r, index, array) => {
    
    if (r[0] !== outputRow[0]) {
      return wlSheet.getRange(arr.length + 2 + index, 7, 1, 2).setValues([[counter, hyperlinkText]]);
    } else {
      return wlSheet.getRange(i + 2, 8).insertCells(SpreadsheetApp.Dimension.COLUMNS).setValue(hyperlinkText);
    }
    });
  });

}

However the code above has resulted into [[PCCS, "9/10/2020", "8/10/2020"], [PCCS, "9/10/2020"], [ASIAPLY, "9/10/2020"], [SCGM, "9/10/2020"]]; instead of desired result.

Is there a way of achieving what I intended to do in Google Apps Script?

Thanks in advance.

google-apps-script google-sheets arrays javascript
2 Answers

If you want to insert an item, you can't use Array.prototype.map as it will return a new array.

Now i'm not familiar with google apps scripts, or interacting with spreadsheets, but the basic JS would look like this:

What you are trying can be easily done with a for...of-loop

The basic steps are:

  • we have two arrays in the shape of a key followed by a number of values
    • if your familiar with TypeScript types: [key: Key, ...values: string[]]
  • we want to take from input to output
    • for each element in input
      • if output has an element with the corresponding key: append own values to it
      • else add self to output
const inArr = [['ASIAPLY', '9/10/2020'], ['PCCS', '9/10/2020'], ['SCGM', '9/10/2020']]
const outArr = [['PCCS', '8/10/2020']]
// iterate over the array elements and use destructuring to
// extract the key form the other values
for (const [key, ...values] of arrayInput) {
  // look for an element in `output` that has that key
  const target = outArr.find(([ky, value]) => ky === key)
  // if we found one, push the values to it
  if (target) target.push(...values)
  // else push your key-values onto the output array
  else outArr.push([key, ...values])
}

The result using the example arrays is:

[
  [ 'PCCS', '8/10/2020', '9/10/2020' ],
  [ 'ASIAPLY', '9/10/2020' ],
  [ 'SCGM', '9/10/2020' ]
]

Since we used spread syntax (...values) in the destructuring, this is little iterator is able to handel 0 or more values by default and will always output the approptiate result.

This should solve this problem in an elegant manner and it is easyly modifiable if needed.

user_129456440

Create a new output array using Map and Array.map:

/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/
const array1 = [
    ['ASIAPLY', '9/10/2020'],
    ['PCCS', '9/10/2020'],
    ['SCGM', '9/10/2020'],
  ],
  array2 = [['PCCS', '8/10/2020']],
  array2Map = new Map(array2),
  arrayOutput = array1.map(([k, v]) => [k, v, array2Map.get(k)]);
console.log(arrayOutput);
<!-- https://meta.stackoverflow.com/a/375985/ -->    <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>

To use setValues, the arrays must be of uniform size.

user_84044530
Related