Google Apps Script, Google Sheets, getRangeList, 3D arrays
In a number of situations in my day-to-day work, I need to grab data from only Selected Columns in Google Sheets. Then using a selected column as a key, remove any duplicates in a similar way that a pivot table does.
I might want to use this information as part of a report or grab emails from a sheet and send custom emails to clients or staff.
I don’t always need all the data in each row, but I may need a lot of it. The two standard approaches would be to:
- Get all the data and then in Google Apps Script just select the relevant columns you need.
- Iterate through a list of columns you want to get ranges of and select each range individually making calls to your Google Sheet in each turn.
This might be useful for small data sets and, to be perfectly honest, I have used both these approaches in the past, but recently I stumbled across a method in the Google Apps Script Sheet Class called:
sheet.getRangeList([A1Notation,...,...])
Now I think that the awesome gods of GAS may not have purposed this method with the thought in mind that it would be used for collecting range values. I kinda get the impression that it was more designed for applying formatting to the selected column ranges.
However, what I have found is that it seems to be pretty fast at collecting all the ranges and then, on the Google Apps Script end, getting their values.
The resulting values of using getRangeList() can be then pushed into a 3D array of columns with row values.
For those of you in a hurry, the code is below. We will move on to an example and explanation after. Feel free to reference what you need and discard the rest.
The Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
/****************************************************************** * Creates a unique list of selected columns (no repeats based on the key_column). * * run() The script: * 1. gets the values of the key_column and personalized_column * 2. Removes all duplicate values and put them in a 2d array. * * * You can add your custom details to the EXTERNAL VARIABLES varialbe area * */ function run() { //EXTERNAL VARIABLES: add your own in the variables below. var startRow = 2 //<<row number var key_column = 3 //<< col number var personalized_columns = [] // col numbers e.g. [1,5,10,15,16] var spreadsheetID = "YOUR SPREADSHEET ID"; var sheetName = "YOUR SHEET NAME"; var ss = SpreadsheetApp.openById(spreadsheetID); var targetSheet = ss.getSheetByName(sheetName); var lastRow = targetSheet.getLastRow(); //Add the key column to the start of the the personalized_columns personalized_columns.unshift(key_column); //Get a 3D array of all column's row values var valsForEachCol = getRanges(targetSheet, startRow, personalized_columns, lastRow); //Get a unique list of results based on the key_column (zeroeth of column of the array); var uniqueList = getUniqueFrom3dArray(valsForEachCol); } //****************************************************************** /* Gets all the columns with GAS getRangeList and then gets the values * of each row of each column and stores it in a 3d array. * * @param sheet {object} : GAS Sheet Class * @param startRow {number} : starting row number * @param pers_cols {array} : array of column Numbers * @param lastRow {number} : end row number * @returns 3d array of row values by columns * */ function getRanges(sheet, startRow, pers_cols, lastRow){ var columnRanges = []; for(var col in pers_cols){ columnRanges.push("R" + startRow + "C" + pers_cols[col] + ":R" + lastRow + "C" + pers_cols[col]); }; //Get columns var rangeOfColumns = sheet.getRangeList(columnRanges); var ranges = rangeOfColumns.getRanges(); var valsForEachCol = []; for(range in ranges){ valsForEachCol.push(ranges[range].getValues()) }; return valsForEachCol; }; //****************************************************************** /* Gets the unique value of a 3D array of columns from the GAS getRangeList method. * The identifying column is always the first array. It then reduces the array to its * unique column value and adds all/any other selected column values to the unique array. * * @param rng {array} : 3D array of columns from GAS getRangeList * @returns 2D array of unique value based on the first column. * */ function getUniqueFrom3dArray(rng){ var unique = []; //rng[0] is the Key Col for(var row in rng[0]){ //Add first row. Need a 1st row to reference against. if(row ==0){ var row_vals = []; //Adds each first row item from each column into first row of 2d array for(var col in rng){ row_vals.push(rng[col][row][0]); }; unique.push(row_vals); }; //Compare against unique array and check there are no matches. var isUnique = true; for(var unq in unique){ if(unique[unq][0] === rng[0][row][0]){ isUnique = false; break; }; }; // If the column 0 value is unique, it stores the values in the 2s array. if(isUnique){ var row_vals = []; for(var col in rng){ row_vals.push(rng[col][row][0]) }; unique.push(row_vals); }; }; return unique; }; |
Quick-Run Guide
The run() function consists of the key variables you need to changes for your own code (Lines 14-19):
startRow: number: row number where the range you want to select starts.key_column: number: column number that you will use as a reference to reduce your data to unique values. For example, students in a section may have a column with a section number, you may wish to select the unique values of just the section without duplicates of the same section.personalized_columns: array: Array of all the columns you want to be selected.spreadsheetID: string: The unique alphanumeric reference to your spreadsheet.sheetName: string: The name of the sheet you wish to reference.
Next, on lines 21-23 we call the selected spreadsheet and the named target sheet tab. We then let Google Apps Script get the last Row number. The code will use the targetSheet sheet object and lastRow number in the getRanges() function.
personalized_columns is then updated to have the key_column at the front of the array. We could have added the these two into the personalized_columns but it is more explicit for the user at the top of the code.
To return the 3D array of each column’s values, we then call the getRanges() function which takes 4 parameters:
targetSheetstartRowpersonalized_columnslastRow
To get the unique values by the first column in the returned getRanges() 3D array we use the getUniqueFrom3dArray() function. This returns a standard 2D array that Google Apps Script will accept when setting values or you might use to iterate through to send emails, share folders or generate reports.
The Example
In our example, we have a store that sells high-end goods to exclusive customers. The store has a spreadsheet of client data including sales and the client’s personalized account manager.
Have a look at the data above ( you can click on the sheet and hold shift + mousewheel to scroll across). Perhaps we want to get a list of Customers and their emails along with the total cost of all purchases in the sheet for each customer, the account manager’s name and the account manager’s phone number.
For the brevity of this example, we’ll just log the results and paste the results in a new sheet… coincidentally, called “Results”.
Here is the updated code. I’ll highlight the changes for you:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 |
/****************************************************************** * Creates a unique list of selected columns (no repeats based on the key_column). * * run() The script: * 1. gets the values of the key_column and personalized_column * 2. Removes all duplicate values and put them in a 2d array. * * * You can add your custom details to the EXTERNAL VARIABLES variable area * */ function run() { //EXTERNAL VARIABLES: add your own in the variables below. var startRow = 2 var key_column = 3 var personalized_columns = [1,2,7,9,12] var spreadsheetID = "1UrTL57Rq6MYLmA9Yy76hA1AxfJDT8s_oDMve7YLeqUc"; var sheetName = "High End Sales"; var destSheetName = "Result"; var ss = SpreadsheetApp.openById(spreadsheetID); var targetSheet = ss.getSheetByName(sheetName); var lastRow = targetSheet.getLastRow(); //Add the key column to the start of the the personalized_columns personalized_columns.unshift(key_column); //Get a 3D array of all column's row values var valsForEachCol = getRanges(targetSheet, startRow, personalized_columns, lastRow); //Get a unique list of results based on the key_column (zeroeth of column of the array); var uniqueList = getUniqueFrom3dArray(valsForEachCol); //Just a simple application of the unique list paste into a cell. //* Example 1 paste the unique values in another sheet. var destSheet = ss.getSheetByName(destSheetName); pasteIntoCell(uniqueList, destSheet); //* Example 2 log the values Logger.log(uniqueList); } //****************************************************************** /* Gets all the columns with GAS getRangeList and then gets the values * of each row of each column and stores it in a 3d array. * * @param sheet {object} : GAS Sheet Class * @param startRow {number} : starting row number * @param pers_cols {array} : array of column Numbers * @param lastRow {number} : end row number * @returns 3d array of row values by columns * */ function getRanges(sheet, startRow, pers_cols, lastRow){ var columnRanges = []; for(var col in pers_cols){ columnRanges.push("R" + startRow + "C" + pers_cols[col] + ":R" + lastRow + "C" + pers_cols[col]); }; //Get columns var rangeOfColumns = sheet.getRangeList(columnRanges); var ranges = rangeOfColumns.getRanges(); var valsForEachCol = []; for(range in ranges){ valsForEachCol.push(ranges[range].getValues()) }; return valsForEachCol; }; //****************************************************************** /* Gets the unique value of a 3D array of columns from the GAS getRangeList method. * The identifying column is always the first array. It then reduces the array to its * unique column value and adds all/any other selected column values to the unique array. * * @param rng {array} : 3D array of columns from GAS getRangeList * @returns 2D array of unique value based on the first column. * */ function getUniqueFrom3dArray(rng){ var unique = []; //rng[0] is the Key Col for(var row in rng[0]){ //Add first row. Need a 1st row to reference against. if(row ==0){ var row_vals = []; //Adds each first row item from each column into first row of 2d array for(var col in rng){ row_vals.push(rng[col][row][0]); }; unique.push(row_vals); }; //Compare against unique array and check there are no matches. var isUnique = true; for(var unq in unique){ if(unique[unq][0] === rng[0][row][0]){ isUnique = false; break; }; }; // If the column 0 value is unique, it stores the values in the 2s array. if(isUnique){ var row_vals = []; for(var col in rng){ row_vals.push(rng[col][row][0]) }; unique.push(row_vals); }; }; return unique; }; //****************************************************************** /* Paste values in range * @param list {array} : 2D uniqueList * @param sheet {object} : GAS sheet class for destination sheet. * */ function pasteIntoCell(list, sheet){ var range = sheet.getRange(1,1,list.length,list[0].length) range.setValues(list); }; |
Let’s see where our first 3 EXTERNAL VARIABLES come from:
When we execute the run() function we will get a logged (line 43) list of unique customers by email, first name, last name, total cost of sale, their accounts manager’s name and their accounts manager’s phone number.
The run() execution also pastes the above-logged values into the “Results” sheet tab. We did this by running our little function pasteIntoCells() that you can see down the bottom of the script.
This makes use of the Google Apps Script range.setValues() method.
Let’s take a brief tour of our two main functions.
getRanges(targetSheet, startRow, personalized_columns, lastRow);
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
//****************************************************************** /* Gets all the columns with GAS getRangeList and then gets the values * of each row of each column and stores it in a 3d array. * * @param sheet {object} : GAS Sheet Class * @param startRow {number} : starting row number * @param pers_cols {array} : array of column Numbers * @param lastRow {number} : end row number * @returns 3d array of row values by columns * */ function getRanges(sheet, startRow, pers_cols, lastRow){ var columnRanges = []; for(var col in pers_cols){ columnRanges.push("R" + startRow + "C" + pers_cols[col] + ":R" + lastRow + "C" + pers_cols[col]); }; //Get columns var rangeOfColumns = sheet.getRangeList(columnRanges); var ranges = rangeOfColumns.getRanges(); var valsForEachCol = []; for(range in ranges){ valsForEachCol.push(ranges[range].getValues()) }; return valsForEachCol; }; |
The getRanges() function takes 4 parameters: sheet, starRow, pers_cols and lastRow.
We first set an empty array on line 15 to catch all the column ranges.
This will store an array of R1C1 notated values. Why? When we use the sheet.getRangeList() method, it requires an array of A1 notated values – well, according to the docs. However, I did discover that it also takes R1C1 notation.
Why didn’t I go for A1 notation? Letters are not fun to use script-side. I’d much rather have column numbers than letters because they are easier to count and add into functions and methods.
Next, we loop through all the selected columns in the pers_cols(line 16). We push the R1C1 values for each column we want into the columnRanges array.
Next on line 21 we insert the newly created list of column ranges and use that as our rangeOfColumns variable which uses our darling of today’s tutorial, sheet.getRangeList(columnRanges).
The next step is to get the values from that range of columns. We first need to get all the ranges from our getRangeList with getRanges(line 23).
We then need to loop through each column range and get the values for each row in each column we’ll then store them in our valsForEachCol = [];
This will generate a 3D array like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[ [ ["emailtitle"], ["email1"], ["email2"], ["email3"], ["...."] ], [ ["firstNameTitle"], ["firstname1"], ["firstname2"], ["firstname3"], ["..."] ], [... [...], //etc ] ] |
This 3D array is then returned to the run() function.
getUniqueFrom3dArray(valsForEachCol);
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
//****************************************************************** /* Gets the unique value of a 3D array of columns from the GAS getRangeList method. * The identifying column is always the first array. It then reduces the array to its * unique column value and adds all/any other selected column values to the unique array. * * @param rng {array} : 3D array of columns from GAS getRangeList * @returns 2D array of unique value based on the first column. * */ function getUniqueFrom3dArray(rng){ var unique = []; //rng[0] is the Key Col for(var row in rng[0]){ //Add first row. Need a 1st row to reference against. if(row ==0){ var row_vals = []; //Adds each first row item from each column into first row of 2d array for(var col in rng){ row_vals.push(rng[col][row][0]); }; unique.push(row_vals); }; //Compare against unique array and check there are no matches. var isUnique = true; for(var unq in unique){ if(unique[unq][0] === rng[0][row][0]){ isUnique = false; break; }; }; // If the column 0 value is unique, it stores the values in the 2s array. if(isUnique){ var row_vals = []; for(var col in rng){ row_vals.push(rng[col][row][0]) }; unique.push(row_vals); }; }; return unique; }; |
This function takes the 3D array generated from the getRanges() function. It uses the first set of values from column one to find all the unique values. In our example, it would take the email column and collect all the unique emails. In all the other corresponding columns it will also get the values and then pack it all into a 2D array with each row containing the key column (e.g. email) and then a list of all the corresponding values.
|
1 2 3 4 5 6 |
[ ["email","first name","last name",10000000,"Account Manager", "555-phone"], [...], [...], ... ] |
On line 12, we first set the unique array variable that we will store all our values.
We then start our main for loop (line 15) that will loop through each row in the zeroeth (first) column.
Before we are able to check to see if the value is unique, we first need to add the first set of row values for each column (lines 17-27). This gives us something to compare against. This section loops through each column and creates an array of all the values on line 0 of each column before pushing it to our unique 2D array.
Next, we need to be able to compare our current unique list against the row values in column 0 to make sure they are unique (lines 29-38). First, we set the condition that the value in column 0 isUnique totrue. We then test this by looping through the list of unique values in our array and checking to see if they exist (lines 31-33). If there is a match, then we want to change the isUnique boolean to false and break out of the unique loop.
However, if the column 0 row value does not exists in the unique array, we want to push the column 0 value and all the other corresponding row values in our other columns into an array. We will then push this unique value into our unique array.
Finally we return the unique array to the main run() function.
Conclusion
I use this set of function in a number of day-to-day tasks like sending out emails, opening edit permissions for docs and sheets and creating custom letters and reports.
Have you made use of this? Do you have a better approach? I would love to hear from you in the comments below.
Need help with Google Workspace development?
My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
~Yagi
Thank you very, very much: quick to adapt / use – much appreciated
Thanks, Marc. It’s great to hear that you could apply it easily.
I’m looking for a way for the code to check existing values in ‘Results’ and only append any new ones instead of overwriting the data already populated in the ‘Results’ tab, including data manually added to the ‘Results’ tab that did not generate from the ‘High End Sales’ tab. Any suggestions on what I could add in to make it check for duplicates and append only unique rows?