0

I have derived aspects of this script from similar topics to improve my knowledge of multi-dimensional arrays through practical use, but I am not sure how to approach the comparison and population step of this script, as I have not yet been able to find a case that covers the use of multi-dimensional arrays within Google Sheets specifically to populate missing data on one sheet with data from another sheet in the way I am thinking of (I may be overthinking a simple problem). What I have currently will output the difference into a new array, but I am not sure how to translate the results in a way that is conducive to adding the data to the correct rows in "Sheet1". Any pointers would be greatly appreciated.

Context:

  • "Sheet1" contains 12 columns of data (A:L) with headers in row 1. "Sheet1" contains several dozen text values that only appear once in Column A and a variety of data in columns B through L
  • "Sheet2" similarly contains 12 columns of data (A:L) with the same headers in row 1. "Sheet2" also contains several dozen text values that only appear once in Column A and a variety of data in columns B through L
  • Some values (ie: "Apple) in "Sheet1" column A will also appear in "Sheet2" column A, but not necessarily in the same row
  • In cases where "Apple" exists in column A on both "Sheet1" and "Sheet2", the values contained on the row "Apple" appears on in "Sheet1" columns B through L may or may not match the values that exist in "Sheet2" columns B through L. In some cases, the values for those columns may be blank/missing in "Sheet1" but present on "Sheet2"

Goal:

  1. Push the values of "Sheet1" A2:L to an array (Array1)
  2. Push the values of "Sheet2" A2:L to an array (Array2)
  3. For each "row" in Array1, if Array1's "column" A value exists in Array2's "column", compare the match's values in columns B through L in Array1 to the match's values in columns B through L in Array2
  4. If a match is not found (or if the data is missing in Array1), populate the missing data in Array1 with the matching data from Array2 and write the results to "Sheet1".

Script:

  var spreadsheet = SpreadsheetApp.getActive();
  var sheet1 = spreadsheet.getSheetByName('Sheet1');
  var sheet2 = spreadsheet.getSheetByName('Sheet2');
  var sheet1DataRange = spreadsheet.getSheetByName('Sheet1').getRange("A2:L");
  var sheet2DataRange = spreadsheet.getSheetByName('Sheet2').getRange("A2:L");

  // Get Sheet1 data
  var sheet1LastRow = sheet1.getRange('A2').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var sheet1Values = sheet1.getRange('A2:L' + currentMonthsheet2LastRow).getValues();

  // Get Sheet2 data
  var sheet2LastRow = sheet2.getRange('A2').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var sheet2Values = sheet2.getRange('A2:L' + sheet2LastRow).getValues();

  // Compare the contents of Sheet1 to Sheet2
  let testingComparison = sheet2Values.concat(sheet1Values);
  let resultArray = testingComparison.filter((item, index) => testingComparison.indexOf(item) === index);
  resultArray.sort();
PatrickdC
  • 1,385
  • 1
  • 6
  • 17
Majora
  • 135
  • 1
  • 2
  • 13
  • 1
    Create a hashmap – TheMaster Mar 03 '23 at 17:47
  • 1
    Please share a copy / sample of your sheet with an example of the desired output. Whenever possible, you need to include a minimal example that reproduces the issue. You can also include the expected behavior, the actual behavior, and how they differ, that would be helpful as well. Please visit [How to Ask](https://stackoverflow.com/help/how-to-ask) have some tips on how to write a question, so the community will be able to help you out in a better way. – Lorena Gomez Mar 03 '23 at 20:57
  • 1
    For the dullards (i.e. me), some explanations/resources for `hashmaps`: [JavaScript hashmap equivalent](https://stackoverflow.com/q/368280/1330560) (17 answers), [Google Apps Script: How to get values from all dynamic keys in deeply nested object](https://stackoverflow.com/a/75038438/1330560), [How is a JavaScript hash map implemented?](https://stackoverflow.com/q/8877666/1330560), [Read a HashMap in App Script](https://stackoverflow.com/a/28028316/1330560), [How can I store and retrieve objects from Google Apps Script Project Properties?](https://stackoverflow.com/a/34302469/1330560) – Tedinoz Mar 07 '23 at 02:42

0 Answers0