Google Apps Script stops during loop instead of continuing to next item

Question

I'm making a script that will populate a Google Sheet with info about our GA4 analytics properties. It starts by listing all our accounts, then looping through them and listing any GA4 properties in them, then looping through the properties and outputting their info to the sheet.

The issue I'm having is when it reaches an account that doesn't contain any GA4 properties, the script just stops instead of continuing to the next account.

I made the script for UA properties first and it worked perfectly, so I'm confused on why it fails in this way just because the API endpoints are different. I've tried changing the "return" to "break" etc. and a bunch of other things but nothing I tried makes any difference.

function init_ga4() {

  // define current sheet as target
  const activeSheet = SpreadsheetApp.getActiveSheet();

  // clear existing data
  activeSheet.getRange("A2:D1000").clear();

  // run main function
  get_ga4_data(activeSheet);
}

function get_ga4_data(activeSheet) {

  // make list of all accounts accessible by current user
  const accounts = Analytics.Management.Accounts.list();

  // set row to start outputting data on
  let r = 2;

  // loop through each account
  for (let i = 0; i < accounts.items.length; i++) {
    
    // get current account idetails
    const account = accounts.items[i];

    // make list of properties in current account
    const propertiesList = AnalyticsAdmin.Properties.list({ filter: "parent:accounts/" + account.id });

    // check if account doesn't have any GA4 properties
    if (propertiesList.properties === undefined) {
      return;

    } else {

      // count properties in list
      const propertiesCount = propertiesList.properties.length;

      // get the range for properties in this accout and output account name
      const accountNameRange = activeSheet.getRange(`A${r}:A${r + (propertiesCount - 1)}`);

      accountNameRange.setValue(account.name);

      const properties = propertiesList.properties;

      // loop through properties and put the property details in the right cells
      for (let p = 0; p < properties.length; p++) {
        const property = properties[p];

        const propertyNameCell = activeSheet.getRange(`C${r + p}`);

        propertyNameCell.setValue(property.displayName);
      }

      // update starting row for next batch of data
      r += propertiesCount;
    } 
  }
}

Answer 1

Although I'm not sure whether I could correctly understand your current issue, in your script, when propertiesList.properties === undefined is true, the script is stopped. I thought that this might be the reason for your current issue of The issue I'm having is when it reaches an account that doesn't contain any GA4 properties, the script just stops instead of continuing to the next account.. In this case, when your showing script is simply modified, how about the following modification?

From:

if (propertiesList.properties === undefined) {
  return;

} else {

To:

if (propertiesList.properties) {

or

if ("properties" in propertiesList) {

or

if (propertiesList.hasOwnProperty("properties")) {

• By this modification, when propertiesList has properties, the script is run.

• But, in your script, setValue is used in a loop. In this case, the process cost becomes high. Ref (Author: me) So, as another modification, how about the following modification? In this modification, get_ga4_data is modified.

  function get_ga4_data(activeSheet) {
    const accounts = Analytics.Management.Accounts.list();
    const values = accounts.items.reduce((ar, { id, name }) => {
      const propertiesList = AnalyticsAdmin.Properties.list({ filter: "parent:accounts/" + id });
      if (propertiesList.properties) {
        propertiesList.properties.forEach(({ displayName }) => ar.push([name, , displayName]));
      }
      return ar;
    }, []);
    activeSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
  }
  

  • When this script is run, the retrieved values are put from "A2" of the active sheet.

References:

• reduce()
• forEach()