I’m trying to create a tree view (and dump that into a Google sheet) of our OrgUnits. I have the following script which pulls and sorts the data:
function listAllOUs() {
const ouArray = []
const page = AdminDirectory.Orgunits.list('my_customer', {
orgUnitPath: '/',
type: 'all'
});
const orgUnits = page.organizationUnits;
if (orgUnits) {
for (let i = 0; i < orgUnits.length; i++) {
const orgUnit = orgUnits[i];
ouArray.push(orgUnit.orgUnitPath)
}
} else {
Logger.log('Could not find any OUs');
}
ouArray.sort()
}
The above code produces the array called ouArray, which looks like this:
[/Parent_01, /Parent_01/Child_01, /Parent_01/Child_01/Grandchild_01, /Parent_01/Child_02, /Parent_01/Child_03, /Parent_01/Child_04, /Parent_02, /Parent_02/Child_01, /Parent_02/Child_02, /Parent_02/Child_02/Grandchild_01, /Parent_02/Child_05, /Parent_02/Child_06, /Parent_02/Child_07, /Parent_02/Child_07/Grandchild_01, /Parent_02/Child_08, /Parent_02/Child_09, /Parent_02/Child_09/Grandchild_01, /Parent_02/Child_3, /Parent_02/Child_4, /Parent_03, /Parent_03/Child_01, /Parent_03/Child_01/Grandchild_01, /Parent_03/Child_02, /Parent_03/Child_02/Grandchild_01, /Parent_03/Child_02/Grandchild_02, /Parent_03/Child_03, /Parent_03/Child_03/Grandchild_01, /Parent_03/Child_03/Grandchild_02, /Parent_03/Child_04, /Parent_03/Child_05, /Parent_03/Child_05/Grandchild_01, /Parent_03/Child_05/Grandchild_02, /Parent_10, /Parent_11]
Now what I want to do is take that data, and format it and place it into a Google Sheet so that it resembles this:
+====+============+=====================+===================================+ | | A | B | C | +====+============+=====================+===================================+ | 1 | /Parent_01 | | | +----+------------+---------------------+-----------------------------------+ | 2 | | /Parent_01/Child_01 | | +----+------------+---------------------+-----------------------------------+ | 3 | | | /Parent_01/Child_01/Grandchild_01 | +----+------------+---------------------+-----------------------------------+ | 4 | | /Parent_01/Child_02 | | +----+------------+---------------------+-----------------------------------+ | 5 | | /Parent_01/Child_03 | | +----+------------+---------------------+-----------------------------------+ | 6 | | /Parent_01/Child_04 | | +----+------------+---------------------+-----------------------------------+ | 7 | /Parent_02 | | | +----+------------+---------------------+-----------------------------------+ | 8 | | /Parent_02/Child_01 | | +----+------------+---------------------+-----------------------------------+ | 9 | | /Parent_02/Child_02 | | +----+------------+---------------------+-----------------------------------+ | 10 | | | /Parent_02/Child_02/Grandchild_01 | +----+------------+---------------------+-----------------------------------+ | 11 | | /Parent_02/Child_05 | | +----+------------+---------------------+-----------------------------------+ | 12 | | /Parent_02/Child_06 | | +----+------------+---------------------+-----------------------------------+ | 13 | | /Parent_02/Child_07 | | +----+------------+---------------------+-----------------------------------+ | 14 | | | /Parent_02/Child_07/Grandchild_01 | +----+------------+---------------------+-----------------------------------+ | 15 | | /Parent_02/Child_08 | | +----+------------+---------------------+-----------------------------------+ | 16 | | /Parent_02/Child_09 | | +----+------------+---------------------+-----------------------------------+ | 17 | | | /Parent_02/Child_09/Grandchild_01 | +----+------------+---------------------+-----------------------------------+ | 18 | | /Parent_02/Child_3 | | +----+------------+---------------------+-----------------------------------+ | 19 | | /Parent_02/Child_4 | | +----+------------+---------------------+-----------------------------------+ | 20 | /Parent_03 | | | +----+------------+---------------------+-----------------------------------+ | 21 | | /Parent_03/Child_01 | | +----+------------+---------------------+-----------------------------------+ | 22 | | | /Parent_03/Child_01/Grandchild_01 | +----+------------+---------------------+-----------------------------------+ | 23 | | /Parent_03/Child_02 | | +----+------------+---------------------+-----------------------------------+ | 24 | | | /Parent_03/Child_02/Grandchild_01 | +----+------------+---------------------+-----------------------------------+ | 25 | | | /Parent_03/Child_02/Grandchild_02 | +----+------------+---------------------+-----------------------------------+ | 26 | | /Parent_03/Child_03 | | +----+------------+---------------------+-----------------------------------+ | 27 | | | /Parent_03/Child_03/Grandchild_01 | +----+------------+---------------------+-----------------------------------+ | 28 | | | /Parent_03/Child_03/Grandchild_02 | +----+------------+---------------------+-----------------------------------+ | 29 | | /Parent_03/Child_04 | | +----+------------+---------------------+-----------------------------------+ | 30 | | /Parent_03/Child_05 | | +----+------------+---------------------+-----------------------------------+ | 31 | | | /Parent_03/Child_05/Grandchild_01 | +----+------------+---------------------+-----------------------------------+ | 32 | | | /Parent_03/Child_05/Grandchild_02 | +----+------------+---------------------+-----------------------------------+ | 33 | /Parent_10 | | | +----+------------+---------------------+-----------------------------------+ | 34 | /Parent_11 | | | +----+------------+---------------------+-----------------------------------+
If anyone has any idea’s on how I can accomplish this, it would be amazing. Logically (at least in my mind), it would need some kind of string/partial string match? But I can’t for the life of me figure it out!
Advertisement
Answer
Explanation:
The logic of the following script is quite simple:
Iterate through
ouArrayand forEach element find the number of forward slashes/.Use a nested ternary operator and for each of the possible cases push the corresponding array to the final
dataarray.
In more detail:*
1x/: append[t,"",""],2x/: append["",t,""],3x/: append["","",t]
where t is every element in ouArray.
*(1x/ means one forward slash etc.)
Complete Solution:
function listAllOUs() {
// get spreadsheet details
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet1'); // choose the name of your sheet
const ouArray = []
const page = AdminDirectory.Orgunits.list('my_customer', {
orgUnitPath: '/',
type: 'all'
});
const orgUnits = page.organizationUnits;
if (orgUnits) {
for (let i = 0; i < orgUnits.length; i++) {
const orgUnit = orgUnits[i];
ouArray.push(orgUnit.orgUnitPath)
}
} else {
Logger.log('Could not find any OUs');
}
ouArray.sort()
data = [];
ouArray.forEach(t=>{
let nslashes = [...t].filter(l => l === '/').length;
data.push(
nslashes==1?[t,"",""]:
nslashes==2?["",t,""]:["","",t]
)
});
// set the values back to the sheet
sh.getRange(1,1,data.length,data[0].length).setValues(data);
}
Reproducible example:
function myFunction() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Sheet1'); // choose the name of your sheet
const ouArray = ["/Parent_01", "/Parent_01/Child_01", "/Parent_01/Child_01/Grandchild_01",
"/Parent_01/Child_02", "/Parent_01/Child_03", "/Parent_01/Child_04",
"/Parent_02", "/Parent_02/Child_01", "/Parent_02/Child_02", "/Parent_02/Child_02/Grandchild_01",
"/Parent_02/Child_05", "/Parent_02/Child_06", "/Parent_02/Child_07",
"/Parent_02/Child_07/Grandchild_01", "/Parent_02/Child_08", "/Parent_02/Child_09"]
data = [];
ouArray.forEach(t=>{
let nslashes = [...t].filter(l => l === '/').length;
data.push(
nslashes==1?[t,"",""]:
nslashes==2?["",t,""]:["","",t]
)
});
sh.getRange(1,1,data.length,data[0].length).setValues(data);
}
Output of reproducible example:
