1

I'm still getting my head around Apps Script and just need some help editing an array.

So I have two arrays:

var arrayBig = SpreadsheetApp.getSheetByName('Big').getDataRange().getValues();

var arraySmall = SpreadsheetApp.getSheetByName('Small').getDataRange().getValues();

And I want to replace specific rows in arrayBig based on values in arraySmall, and then write that array back to my spreadsheet (the last part I'm ok with).

The two arrays have the same number of columns, but arraySmall has fewer rows.

The rows to replace:

  • Based on the numeric value in Column 1 in arraySmall replace that row in arrayBig.

So if the value in Column 1 = 3 replace row number 3 in arrayBig with the contents of the row from arraySmall where Column 1 = 3.

I think the answer has to do with the map method but I don't understand it. Just some pointers to get me started would be much appreciated, thank you.

Improve this question

2 Answers 2

Reset to default
1

Here is one "get you started" approach:

You describe your data, but you do not provide any concrete samples - so here is my starting data, making some assumptions based on the information in the question:

var arrayBig = [ [1, "A", "B"], [2, "C", "D"], [3, "E", "F"], [4, "G", "H"] ];
var arraySmall  = [ [1, "P", "Q"], [3, "Y", "Z"] ];

With the above data, the expected outcome is the following, where rows 1 and 3 are replaced:

[ [1, "P", "Q"], [2, "C", "D"], [3, "Y", "Z"], [4, "G", "H"] ];

Here is the approach:

var arrayBig = [ [1, "A", "B"], [2, "C", "D"], [3, "E", "F"], [4, "G", "H"] ];
var arraySmall  = [ [1, "P", "Q"], [3, "Y", "Z"] ];

var mapper = new Map();
arraySmall.forEach((row) => { 
  mapper.set(row[0], row);
} );

newArrayBig = [];
arrayBig.forEach((row) => { 
  if ( mapper.has( row[0] ) ) {
    newArrayBig.push( mapper.get( row[0] ) );
  } else {
    newArrayBig.push( row );
  }
} );

console.log( newArrayBig );

This assumes you have an iron-clad guarantee that there are never more rows of data in arraySmall than there are in arrayBig. You can (and should) add some logic to test for that, for more robust code.

Notes

The Map object (not the map() method) is central to this approach. This object provides a lookup structure: an index value, pointing to a data value:

var mapper = new Map();
arraySmall.forEach((row) => { 
  mapper.set(row[0], row);
} );

In our case, the index is the number in the first cell of each arraySmall row. And the value it points to is the complete data array for that row, for example:

1 -> [1, "P", "Q"]
3 -> [3, "Y", "Z"]

We can now use this lookup data as we iterate through each row of our arrayBig data:

arrayBig.forEach((row) => { ... } );

The logic inside this forEach iterator basically says: If the map contains an array using the same number as the current arrayBig row, then use the arraysmall data. Otherwise, use the arrayBig data.

The newArrayBig.push() method adds that array to our results array.

You can then write this back to your spreadsheet, as needed.

Improve this answer
Sign up to request clarification or add additional context in comments.

17 Comments

Thank you @andrewjames, you've understood what I want exactly. To confirm: "this assumes you have an iron-clad guarantee that there are never more rows of data in arraySmall than there are in arrayBig." - that is correct, possibly an equal number but never more. I look forward to getting to grips with your answer and applying it to my spreadsheet, thank you!
Thank you again, I'm playing around to try to understand the function; changing the index column and changing the order of the arraySmall - and it's working really well. I like that it's basically an Apps Script version of an index(match) formula (or vlookup). So the arraySmall doesn't have to be sequential, for instance var arraySmall = [ [3, "Y", "Z"], [1, "P", "Q"] ]; also works fine. I don't quite understand Map() and Map().set(key, value) I mean I can see what happens, I just don't get them exactly. Do you know of a good documentation link? thank you!
Conceptually, you are correct, a JavaScript map is like a vlookup. A phone directory is a type of map: It lets you use a key (a person's name) to quickly find a value (their phone number). When you use set() you are adding an entry to the phone directory (or replacing the existing entry): myDirectory.set('Smith', '123-45-67'). And when you use myDirectory.get('Smith'), you retrieve the phone number. This retrieval is efficient/fast, compared to searching an array.
Documentation: I often use this, but there are many others, such as this, which is part of an overall tutorial site. You should try different resources and find the ones that you like the best. As you can see from the answer by @YuriKhristich, there are ways to combine functions and operators in JavaScript which can make your code very compact - which is great.
Thank you, really clear. So far in my limited experience using Apps Script I've used setValues() or getValues() to write to the sheet, rather than use JavaScript maps. But now I've a need to do something a little more complex (well, complex for me!) and this is much faster. Thank you for the links as well.
|
1

Just in case. Another solution:

var arrayBig    = [ [1,"A","B"], [2,"C","D"], [3,"E","F"], [4,"G","H"] ];
var arraySmall  = [ [1,"P","Q"], [3,"Y","Z"] ];

var obj = {}; // it will be { "1":[1,"P","Q"], "3": [3,"Y","Z"] }

arraySmall.forEach( x => obj[x[0]] = x );

arrayBig = arrayBig.map( x => obj[x[0]] || x );

console.log(arrayBig); // [[1,"P","Q"], [2,"C","D"], [3,"Y","Z"], [4,"G","H"]];

It converts the small array into an object where first element of every row is a key and the row is its value. Then it loops through the big array and tries to get values of the object by keys (a first cell). If the key exists the row will replace, if key doesn't exist the for wont change.

Sequence of the rows in the small array doesn't matter as well.

Updage

You can use an array instead of an object if you want:

var arrayBig = [ [1,"A","B"], [2,"C","D"], [3,"E","F"], [4,"G","H"] ];
var arraySmall  = [ [1,"P","Q"], [3,"Y","Z"] ];

var arr = [];

arraySmall.forEach( x => arr[x[0]-1] = x ); // [ [1,"P","Q"], [], [3,"Y","Z"], [] ];

arrayBig = arrayBig.map( (x,i) => arr[i] || x);

console.log(arrayBig);

I think it will take more memory, but it works. But you need to be sure that first cells contain numbers only, since they're using as indexes of the array (indexes of array should be numbers, unlike keys that can be anything). And rows of first array should be sorted and has no skipped numbers (1, 2, 3, 4, etc). So, it's a more fragile solution. Just for educational purposes.

Improve this answer

3 Comments

Thank you @YuriKhristich, yes this also works. I'm curious about using an object, as opposed to an array (I'm not familiar with objects!), and also how arrayBig.map() is working. It looks like a different way of writing 'if else' that I'm not familiar with either.
In this case the object is like an array, but you you can get elements of the 'array' via 'keys' not via indexes. So obj[key] = value is the same as array[index] = value. First cells of the rows of your array are keys. Rows are values. arrayBig.map() is just a short variant of a loop. It can be replaced with: for (i=0;i<arrayBig.length;i++) {var key = arrayBig[i][0]; arrayBig[i] = obj[key] || arrayBig[i];}
Interesting, thank you for the explanation

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.