Export Chromebook device list using Google Script

Question

I created this function to export Chromes device list through a Google Admin portal. It collects devices and filters by PRE_PROVIONED status. However, when trying to run all pages (130,000 devices) the error "Out of memory error." is being returned. How to fix this error? If I remove the pagination (nextPageToken), it works normally.

function exportChromebooksToSheet() {
  // Adicione os escopos necessários
  const maxResults = 50;  // ajuste conforme necessário
  let pageToken;

  const allDevices = [];

  do{
    const options = {   
          orderBy: 'serialNumber',
          maxResults: maxResults,
          pageToken: pageToken
        };

    // Obtenha a instância do serviço de Administração
    const response = (AdminDirectory.Chromeosdevices.list("my_customer", options));
    let devices = response.chromeosdevices;

    // Adicione os dispositivos da página atual à lista completa
    allDevices.push(...devices);

    // Atualize o token de página para a próxima página
    pageToken = response.nextPageToken;

  } while (pageToken);
  
      
 // Filtrar dispositivos com status PRE_PROVISIONED
  const preProvisionedDevices = devices.filter(function(device) {
    return device.status === 'PRE_PROVISIONED';
  });

  writeToSheet(preProvisionedDevices);
}

function writeToSheet(devices) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Limpe o conteúdo atual da planilha
  sheet.clear();

  // Escreva os cabeçalhos
  var headers = ['Serial Number', 'Status'] // Adicione mais campos conforme necessário
  sheet.appendRow(headers);

  // Escreva os dados dos dispositivos
  devices.forEach(function (device) {
    var rowData = [
      device.serialNumber,
      device.status,
      
      // Adicione mais campos conforme necessário
    ];

      sheet.appendRow(rowData);

    
  });
}

I tried setting maxResults with different parameters and nothing happened. If I remove the pageToken and put maxResults with any value, it always returns 42

Answer 1

I am by no means an expert in Apps Script, but here is how I solve this issue in my Org.

I do want to first mention that if automation is not your goal, a vendor's sheet plugin called Gopher can do what you asked, just not automatically.

Now for automation, that adds some complexity. That error is occurring because your array "allDevices" is holding so many devices that the Apps Script runtime can't handle it. It's the array's size that causes your error. The solution is to periodically dump a smaller chunk of those devices into something else, like Google Sheets or Google Drive.

Now, a secondary issue you'll have is that since you have over ~100,000 devices, you will also eventually get a timeout error. Apps Script can only run for 30 minutes, and if you notice, every time you run "AdminDirectory.Chromeosdevices.list", it takes around 2 seconds and only returns 100 devices at most. That means that for 30 minutes, the theoretical maximum devices you can get is 90,000. In practice, I often get less than that.

That means that to solve your problem, you will need to run the script multiple times, and also save your results in multiple smaller chunks. Shown below is some heavily commented psuedo code that stores the page token in your code, allowing it to run multiple times. In my case, I get around 32,000 results per script run, so I hardcode my Apps Script to run 3 times every night. In your case, you'll make as many executions as you need to make it work in Sheets.

// The biggest complexity is getting the script to run multiple times
// My solution is to store the page token in Drive, and use that to allow multiple runs in a row
// I do multiple runs in a row by counting how many runs I need, and then making consequtive triggers for them
// This means that if I get 30,000 results per run, then my script needs to run 3 different triggers to reach ~90,000.
// A better coder could have the script retrigger itself
const folder = DriveApp.getFolderById("INPUT_YOUR_FOLDER_HERE");
const folderdirectory = folder.getFiles();
while (folderdirectory.hasNext()) { // This loops through each file to get the page token in the folder
    // I store the page token in the same folder as the Google Sheet, but you don't have to
    const file = folderdirectory.next();
    if (!file.getName().endsWith(".gsheet")) {// The unfinished token is not a sheet
      pageToken = file.getName(); //Sets PageToken to filename of PageToken file
      file.setTrashed(true); // Deletes the Page Token file to make way for a new one on next run
    }
}

// Next, you need to hardcode your limit on how many devices per run. In this example, I choose 30,000
// countOfResults will start at 0 and incremement until 30,000, then stopping
let countOfResults = 0;

    do {
      const options = { // As mentioned later, you likely should put your preprovisioned filter here
            orderBy: 'serialNumber',
            maxResults: maxResults,
            pageToken: pageToken
          };
  
      // Obtenha a instância do serviço de Administração
      const response = (AdminDirectory.Chromeosdevices.list("my_customer", options));
      let devices = response.chromeosdevices;
      // We now immediately write this to sheets. This does take longer, but we now run multiple times, so it doesn't matter too much
      // You can also use Sheets.getRange().setValues() to speed this up, but I could not get it to work personally
      appendToSheet(devices);
      countOfResults += 100;//Incremement our counter so we can stop before our time limit
  
      // Atualize o token de página para a próxima página
      pageToken = response.nextPageToken;
  
    } while ((pageToken) && (countOfResults < 30000)); // When we run out of page tokens, the executions stop for real
    // but if we've just hit the limit, we use the below code to get ready to start again

    if ((typeof pageToken !== "undefined")) { // If there still is another pageToken, setup next run
    //Store Page Token for next Run
    const filename = String(pageToken);
    folder.createFile(filename, filename);
    }
  
  }
  
  function appendToSheet(devices) { // We can't just write it all at once, so we have to append
  // You will need to write code to reset the report when a fresh start is needed, rather than an append
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // You could use SpreadsheetApp.openById to let this always run, even if you don't have sheets open
    // But if you plan to only run this script with Sheets actively open, I still recommend Gopher
  
    // Escreva os dados dos dispositivos
    devices.forEach(function (device) {
      var rowData = [
        device.serialNumber,
        device.status,
        
        // Adicione mais campos conforme necessário
      ];
  
        sheet.appendRow(rowData);
  
    });
  }

The above code shows the changes you'll need to make to solve your error issue. I won't go over storing it in Sheets, since that is relatively easy to do comparatively and isn't the crux of the question, but I do want to let you know that you can call a filter for ChromeOSDevice.list like shown below:

const options = {
  "maxResults": 100,
  "pageToken": pageToken,
  "query": "status:managed",
  "projection": "BASIC"
}

// Get devices from Admin Directory
const response = AdminDirectory.Chromeosdevices.list('XXXXXXX', options);

That filter will let you filter on the Provision Statuses provided by google.

Lastly, the main reason I don't have example code for Sheets is that I stored it in JSON for my org, rather than sheets. Just incase it helps, below is my fully operation code that stores JSON files rather than sheets. I then use Power BI to combine the JSON, but any ETL tool would work:

function exportChromebooksToDrive() {
  // Setup iteration for storing multiple pages of Device results
  let pageToken;
  let devicesChunk = []; // Holds individual pages in larger array for bulk exporting
  let countOfFilesInDrive = 0; // Lets us control how many chunks are created per script run

  // Get current pageToken
  const folder = DriveApp.getFolderById("XXXXXXXXXXXXXXXXXXXXX");
  const folderdirectory = folder.getFiles();
  while (folderdirectory.hasNext()) { //Checks each file for if it's unfinished token
      const file = folderdirectory.next();
      if (!file.getName().endsWith(".json")) {// The unfinished token is not JSON
        pageToken = file.getName();
        file.setTrashed(true);
      }
  }

  do {
    const options = {
      "maxResults": 100,
      "pageToken": pageToken,
      "projection": "FULL"
    }

    // Get devices from Admin Directory
    const response = AdminDirectory.Chromeosdevices.list('XXXXXXXXXXXXXXXXXXXXX', options);

    // Append current page of results to array
    devicesChunk.push(response);

    // Flushes json chunk into Drive when too big
    if (devicesChunk.length >= 40) {
      folder.createFile(pageToken.concat(".json"), devicesChunk);
      devicesChunk.length = 0; // Erases contents of array
      countOfFilesInDrive++; // Increments the number of times we've dumped Devices into Drive
      console.log(countOfFilesInDrive);
    }

    // Flushes Chunk if final Page Token to prevent lost data
    if ((typeof response.nextPageToken == "undefined") && (countOfFilesInDrive !== 0)) {
      folder.createFile(pageToken.concat(".json"), devicesChunk);
      devicesChunk.length = 0; // Erases contents of array
      countOfFilesInDrive++; // Increments the number of times we've dumped Devices into Drive
    }
  
    // Get next page setup
    pageToken = response.nextPageToken;
  } while ((typeof pageToken !== "undefined") && (countOfFilesInDrive < 8)); // 8 Chunks of 40 Pages with MaxResult 99 = 32,000 devices per run

  if ((typeof pageToken !== "undefined")) { // If pageToken is existant, setup next run
    //Store Page Token for next Run
    const filename = String(pageToken);
    folder.createFile(filename, filename);
  }

  // All of this runs 3 times a night automatically

}