Search code examples
htmlgoogle-apps-scriptgoogle-analytics

onChange function not firing with one option in the dropdown

I have three drop down fields, one is pulling list of accounts and another drop down field is pulling the list of properties associated with that account. Once the property is selected, the third drop down is populated.

The second drop down field will get populated when the account value is selected. The third drop down value will get selected when second drop down value is selected.

Issue:

I select an account, the second box gets populated with a value. Let's say the value is ABC. My desired selection for second box is ABC, now in order to populate the third drop down, we need select2 function to trigger. So I open the dropdown and select another value and then select ABC again to trigger the function

Explained Visually.

enter image description here

//Account Drop Down

//Sample Values - getAccounts()

[ { createTime: 'xxxxx',
    regionCode: 'SA',
    displayName: 'ABC',
    name: 'accounts/XXXX',
    updateTime: 'xxxx' } ]

<div class="form-group">
  <label for="accountData">Select Account:</label>
  <select id="accountData" style="width: 100%" onchange="select1(this)">
    <? var data = getAccounts () ?>
    <? for (i=0; i<data.length; i++) { ?>
      <option>Select Account</option>
      <option value="<?= data[i].name.split('/')[1] ?>" ><?= data[i].displayName ?></option>
      
    <? } ?>
  </select>

  </div>


//Property Field Drop Down

<div class="form-group">
  <label for="accountData2">Select Property:</label>
  <select id="accountData2" style="width: 100%" onchange="select2(this)">

    <option>Select Properties</option>
  </select>
</div>

//Third Dropdown
<div class="form-group">
  <label for="accountData3">Select Data Streams:</label>
  <select id="accountData3" style="width: 100%" onchange="">

    <option>Select Streams</option>
  </select>
</div>


function select1(e) {
  google.script.run.withSuccessHandler(ar => {
    
    const select = document.getElementById("accountData2");
    select.innerHTML = "";
    
    if (ar.length == 0) return;
    
    ar.properties.forEach(item => {
      
      const option = document.createElement("option");
      option.text = item.displayName;
      option.value = item.name.split('/')[1];
      select.appendChild(option);
      
    });
  }).withFailureHandler(er => {

    alert('This is error:' + er);
    
  }).listProperties(e.value);

}


function select2(e) {

  console.log(`${e.value} was selected.`);


google.script.run.withSuccessHandler(ar => {
    console.log(ar)
    const select = document.getElementById("accountData3");
    select.innerHTML = "";
    
    if (ar.length == 0) return;
    
    ar.forEach(item => {
      
      const option = document.createElement("option");
      option.text = item.displayName;
      option.value = item.name.split('/')[3];
      select.appendChild(option);
      
    });
  }).withFailureHandler(er => {

    alert('This is error:' + er);
    
  }).listDataStreams(e.value);





}
Solution

  • From your provided whole script, how about the following modification?

    Google Apps Script side:

    function onOpen() {
  var menu = SpreadsheetApp.getUi().createAddonMenu();
  menu.addItem('Launch Audit', 'showSidebar');
  menu.addToUi();
}

function showSidebar() {
  const html = HtmlService.createTemplateFromFile('audit');
  html.options = getAccounts();
  SpreadsheetApp.getUi().showSidebar(html.evaluate().setTitle('GA4 Audit'));
}

function getAccounts() {
  var accounts = AnalyticsAdmin.Accounts.list().accounts;
  var res = "<option>Select Account</option>" + accounts.map(({ name, displayName }) => `<option value="${name.split("/")[1]}" >${displayName}</option>`).join("");
  return res;
}

function listProperties(accountId) {
  var properties = AnalyticsAdmin.Properties.list({ filter: 'parent:accounts/' + accountId });
  return properties;
}

function listDataStreams(property) {
  var streams = AnalyticsAdmin.Properties.DataStreams.list('properties/' + property);
  var id = streams.dataStreams;
  return id;
}

    HTML & Javascript side:

    <!DOCTYPE html>
<html>

<head>

  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
  <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />

  <base target="_top">

  <!-- Custom Styles -->
  <style>
    body {
      padding: 20px;
    }

    h1 {
      line-height: 120%;
    }

    .width90 {
      width: 90%;
    }

    .form-group {
      margin-bottom: 20px;
      margin-top: 20px;
      /* padding: 20px; */

    }
  </style>
</head>

<body>
  <div class="form-group">
    <label for="accountData">Select GA4 Account:</label>
    <select id="accountData" style="width: 100%" onchange="select1(this)">
    <?!= options ?>
  </select>
  </div>
  <div class="form-group">
    <label for="accountData2">Select Property:</label>
    <select id="accountData2" style="width: 100%" onchange="select2(this)">
    <option>Select Properties</option>
  </select>
  </div>
  <div class="form-group">
    <label for="accountData3">Select Data Streams:</label>
    <select id="accountData3" style="width: 100%" onchange="">
    <option>Select Streams</option>
  </select>
  </div>
  <div class="form-group">
    <button id="btn">Export</button>
  </div>

<script>
function select1(e) {
  if (e.value == "Select Account") {
    document.getElementById("accountData2").innerHTML = "<option>Select Properties</option>";
    document.getElementById("accountData3").innerHTML = "<option>Select Streams</option>";
    return;
  }
  google.script.run.withSuccessHandler(ar => {
    const select = document.getElementById("accountData2");
    select.innerHTML = "";
    if (ar.length == 0) return;
    ar.properties.forEach((item, i) => {
      if (i == 0) {
        const option = document.createElement("option");
        option.text = "Select Properties";
        select.appendChild(option);
      }
      const option = document.createElement("option");
      option.text = item.displayName;
      option.value = item.name.split('/')[1];
      select.appendChild(option);
    });
  }).withFailureHandler(er => {
    alert('This is error:' + er);
  }).listProperties(e.value);
}


function select2(e) {
  if (e.value == "Select Properties") {
    document.getElementById("accountData3").innerHTML = "<option>Select Streams</option>";
    return;
  }
  google.script.run.withSuccessHandler(ar => {
    const select = document.getElementById("accountData3");
    select.innerHTML = "";
    if (ar.length == 0) return;
    ar.forEach((item, i) => {
      if (i == 0) {
        const option = document.createElement("option");
        option.text = "Select Streams";
        select.appendChild(option);
      }
      const option = document.createElement("option");
      option.text = item.displayName;
      option.value = item.name.split('/')[3];
      select.appendChild(option);
    });
  }).withFailureHandler(er => {
    alert('This is error:' + er);
  }).listDataStreams(e.value);
}
</script>
</body>
</html>

    • In this modification, when showSidebar() is run, a sidebar is opened on Google Spreadsheet. The flow is as follows.

      1. Values are loaded to the 1st dropdown list of "Select GA4 Account:".
      2. When "Selected Account" of the dropdown list of "Select GA4 Account:" is changed, the values related to the 1st dropdown list are loaded to the 2nd dropdown list of "Select Property:".
      3. When "Select Properties" of the 2nd dropdown list is changed, the values related to the 2nd dropdown list are loaded to the 3rd dropdown list of "Select Data Streams:".
      4. By this, you can select from "Select Streams" of the 3rd dropdown list.

    • In this case, even when the response value is only one, the 1st values of the 3 dropdown lists are "Selected Account", "Select Properties", and "Select Streams", respectively. By this, you can select the response value from the dropdown list and the onChange event can be fired.