Create tree from 2D array and update Google Sheet

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!

Answer

Explanation:

The logic of the following script is quite simple:

• Iterate through ouArray and 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 data array.

In more detail:*

• 1 x /: append [t,"",""],
• 2 x /: append ["",t,""],
• 3 x /: 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: