App Version filter is not working

[La Yaung Chel]

My script is not working to filter the app version with does not contain condition. But it can successfully worked in GA4.

Here is my code. 

function signWithPrivateKey(input, privateKey) {

  const signature = Utilities.computeRsaSha256Signature(input, privateKey);

  return Utilities.base64EncodeWebSafe(signature);

}

function createRequestPayload(propertyId, dateStr, funnelSteps) {

  return {

    property: `properties/${propertyId}`,

    dateRanges: [{ startDate: dateStr, endDate: dateStr }],

    funnel: { steps: funnelSteps }

  };

}

// Function to send the request and process the funnel report data

function getFunnelReportData(accessToken, propertyId, requestPayload) {

  const url = `https://analyticsdata.googleapis.com/v1alpha/properties/${propertyId}:runFunnelReport`;

  const options = {

    method: 'POST',

    contentType: 'application/json',

    headers: {

      Authorization: 'Bearer ' + accessToken,

    },

    payload: JSON.stringify(requestPayload),

  };

  const response = UrlFetchApp.fetch(url, options);

  return JSON.parse(response.getContentText());

}

// Function to process the response data into rows

function processFunnelReportData(responseData, dateStr) {

  if (!responseData || !responseData.funnelTable) {

    Logger.log("Invalid funnel response:");

    Logger.log(JSON.stringify(responseData, null, 2));

    return { rows: [], headers: [] };

    }

  const rows = responseData.funnelTable.rows.map(row => {

    const dimensions = row.dimensionValues.map(dim => dim.value);

    const metrics = row.metricValues.map(metric => parseFloat(metric.value));

    return dimensions.concat(metrics);

  });

  // Clean dimension headers

  const dimensionHeaders = responseData.funnelTable.dimensionHeaders.map(header => {

    let name = header.name;

    if (name.toLowerCase().startsWith("funnelstep")) {

      name = name.replace(/^funnelStep/, "");

      name = name.charAt(0).toLowerCase() + name.slice(1);

    }

      return name;

  });

  // Clean metric headers and remove duplicates

  const metricHeaders = [];

  const seenHeaders = new Set();

  responseData.funnelTable.metricHeaders.forEach(header => {

    let name = header.name;

    if (name.toLowerCase().startsWith("funnelstep")) {

      name = name.replace(/^funnelStep/, "");

      name = name.charAt(0).toLowerCase() + name.slice(1);

    }

    if (!seenHeaders.has(name)) {

      metricHeaders.push(name);

      seenHeaders.add(name);

      }

    });

  const headers = [...dimensionHeaders, ...metricHeaders, 'date'];

  rows.forEach(row => row.push(dateStr));

  return { rows, headers };

}

// Function to write data to the Google Sheet

function writeToSheet(sheetName, headers, rows) {

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  let sheet = spreadsheet.getSheetByName(sheetName);

  // If the sheet doesn't exist, create it

  if (!sheet) {

    sheet = spreadsheet.insertSheet(sheetName);

    // Write headers only if creating a new sheet

    sheet.appendRow(headers);

  }

  // Append data to the end of the sheet

  rows.forEach(row => sheet.appendRow(row));

}

// Main function to run the funnel report

function runFunnelReport(dateStr,funnelSteps,shname) {

  const accessToken = getServiceAccountAccessToken();

  const propertyId = "186609758";  // Replace with your Property ID

 

  // Logger.log(funnelSteps);

  // Create the request payload

  const requestPayload = createRequestPayload(propertyId, dateStr, funnelSteps);

  // Get the funnel report data

  const responseData = getFunnelReportData(accessToken, propertyId, requestPayload);

  // Process the data into rows and headers

  const { rows, headers } = processFunnelReportData(responseData, dateStr);

  // Write data to the sheet

  writeToSheet(shname, headers, rows);  // Replace with your desired sheet name

}

function createFunnelStep(conditions) {

  return {

    filterExpression: {

      andGroup: { expressions: conditions }

    }

  };

}

function runFunnelReportForDateRange(startDate, endDate,funnelSteps,shname) {

  const start = new Date(startDate); // Start date

  const end = new Date(endDate); // End date

  const timeZone = Session.getScriptTimeZone();

  // Loop through each date in the range

  for (let date = start; date <= end; date.setDate(date.getDate() + 1)) {

    const dateStr = Utilities.formatDate(date, timeZone, 'yyyy-MM-dd');

    runFunnelReport(dateStr,funnelSteps,shname); // Call the function for the specific date

  }

}

function deleteDuplicateRowsByColumns(sheetName, compareColumns) {

  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet

  const sheet = ss.getSheetByName(sheetName); // Get the sheet by name

  if (!sheet) {

    throw new Error(`Sheet "${sheetName}" not found.`);

  }

  const data = sheet.getDataRange().getValues(); // Get all the data in the sheet

  const headers = data[0]; // Assume the first row contains headers

  const dataWithoutHeaders = data.slice(1); // Exclude headers for processing

  const uniqueMap = new Map();

  // Convert column names to indices if provided as strings

  const columnIndices = compareColumns.map((col) =>

    typeof col === "string" ? headers.indexOf(col) : col - 1

  );

  if (columnIndices.some((index) => index < 0)) {

    throw new Error("One or more column names provided do not exist in the headers.");

  }

  // Process rows and store only the last occurrence of duplicates

  for (let i = 0; i < dataWithoutHeaders.length; i++) {

    const row = dataWithoutHeaders[i];

    const key = columnIndices.map((index) => row[index]).join("|"); // Create a unique key using specified columns

    uniqueMap.set(key, row); // Update the map with the latest row for the key

  }

  // Get the unique rows in their original order

  const uniqueData = Array.from(uniqueMap.values());

  // Write back to the sheet: clear existing data and replace with unique rows

  sheet.clearContents();

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]); // Restore headers

 

  sheet

    .getRange(2, 1, uniqueData.length, uniqueData[0].length)

    .setValues(uniqueData);

  Logger.log(

    `Duplicates removed from sheet "${sheetName}" based on columns: ${compareColumns.join(", ")}.`

  );

}

function checkAndRepeatForMissingDays(startDateStr, endDateStr,funnelSteps,shname) {

  // Parse input parameters

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shname); // Sheet name

  if (!sheet) {

    Logger.log(`Sheet "${shname}" not found!`);

    return;

  }

  const dateColumn = 6; // Assuming dates are in Column A (1-indexed)

  const startRow = 2; // Start reading from Row 2 (skip header if present)

  const startDate = new Date(startDateStr); // Parse start date

  const endDate = new Date(endDateStr); // Parse end date

  if (isNaN(startDate) || isNaN(endDate)) {

    Logger.log("Invalid startDate or endDate format! Use YYYY-MM-DD.");

    return;

  }

  // Generate a set of all dates in the range

  const allDates = [];

  let currentDate = new Date(startDate);

  while (currentDate <= endDate) {

    allDates.push(formatDate(currentDate)); // Format as string for comparison

    currentDate.setDate(currentDate.getDate() + 1); // Increment day

  }

  // Fetch dates from the column

  const lastRow = sheet.getLastRow();

  const dateValues = sheet.getRange(startRow, dateColumn, lastRow - startRow + 1, 1).getValues();

  const sheetDates = dateValues

    .flat()

    .filter((date) => date) // Remove empty cells

    .map((date) => formatDate(new Date(date))); // Format for comparison

  // Find missing dates

  const missingDates = allDates.filter((date) => !sheetDates.includes(date));

  // Logger.log("Missing Dates: " + missingDates);

  // Perform actions for missing dates

  missingDates.slice(0, 6).forEach((missingDate) => {

    performActionForDate(formatDate(new Date(missingDate)) ,funnelSteps,shname);

  });

}

// Normal equality filter

function filter(field, value) {

  return {

    filter: {

      fieldName: field,

      stringFilter: { matchType: 'EXACT', value: value }

    }

  };

}

function exclude(field, value) {

  return { notExpression:

    { filter:

      { fieldName: field,

        stringFilter: { matchType: "EXACT", value: value }

      }

    }

  };

}

function formatDate(date) {

  const year = date.getFullYear();

  const month = String(date.getMonth() + 1).padStart(2, "0");

  const day = String(date.getDate()).padStart(2, "0");

  return `${year}-${month}-${day}`;

}

// Example action to perform for a missing date

function performActionForDate(dateStr,funnelSteps,shname) {

  // runFunnelReportForDateRange(date, date,funnelSteps,shname);

  runFunnelReport(dateStr,funnelSteps,shname);

}

function DXP_home_tab_service() {

  const twoDaysAgo = new Date();

  twoDaysAgo.setDate(twoDaysAgo.getDate() - 2);

  const dateStr = Utilities.formatDate(twoDaysAgo, Session.getScriptTimeZone(), 'yyyy-MM-dd');

  const homePackIcon = [

    createFunnelStep("Home Screen", "mpt_page", [[["pageName", "Home"]]]),

    createFunnelStep("BottomNavi_Service","mpt_event",[[["category","Home"],["action", "Tab bar/ဝန်ဆောင်မှု"]],[["category","Home"],["action", "Tab bar/Services"]]]),

    createFunnelStep("Package-icon", "mpt_event",[[["category","Service"],["label", "ပက်‌ကေ့ချ်"]],[["category","Service"],["label", "Package"]]]),

    createFunnelStep("Package Order Confirmation", "mpt_page",[[["pageName", "Package Order Confirmation"]],[["DialogName", "Order Confirmation"]]]),

    createFunnelStep("Package Order Success", "mpt_page", [[["pageName", "Package Order Success"]],[["DialogName", "Package Order Success"]]])

  ];

  const nonDXPConversion = [

    // createFunnelStep("Home Screen", "mpt_page", [[["pageName", "Home"],["appVersion", "!4.5.7"]],[["pageName", "Home"],["appVersion", "!4.5.8"]],[["pageName", "Home"],["appVersion", "!4.5.9"]],[["pageName", "Home"],["appVersion", "!4.6.0"]],[["pageName", "Home"],["appVersion", "!4.6.1"]]]),

    createFunnelStep([ filter("pageName", "Home"), exclude("appVersion", "4.5.8"), exclude("appVersion", "4.5.7"), exclude("appVersion", "4.5.9"), exclude("appVersion", "4.6.0"), exclude("appVersion", "4.6.1") ]),

    createFunnelStep("BottomNavi_Service","mpt_event",[[["category","Home"],["action", "Tab bar/ဝန်ဆောင်မှု"]],[["category","Home"],["action", "Tab bar/Services"]]]),

    createFunnelStep("Package-icon", "mpt_event",[[["category","Service"],["label", "ပက်‌ကေ့ချ်"]],[["category","Service"],["label", "Package"]]]),

    createFunnelStep("Order Confirmation", "mpt_page",[[["pageName", "Package Order Confirmation"]]]),

    createFunnelStep("Order Confirmation Dialog", "mpt_page",[[["pageName", "Package Order Confirmation Dialog"]]]),

    createFunnelStep("Package Order Success", "mpt_page", [[["pageName", "Package Order Success"]]])

  ];

  // const dxpConversion = [

  //   createFunnelStep("Home Screen", "mpt_page", [[["pageName", "Home"],["appVersion", "4.5.7"]],[["pageName", "Home"],["appVersion", "4.5.8"]],[["pageName", "Home"],["appVersion", "4.5.9"]],[["pageName", "Home"],["appVersion", "4.6.0"]],[["pageName", "Home"],["appVersion", "4.6.1"]]]),

  //   createFunnelStep("BottomNavi_Service","mpt_event",[[["category","Home"],["action", "Tab bar/ဝန်ဆောင်မှု"]],[["category","Home"],["action", "Tab bar/Services"]]]),

  //   createFunnelStep("Package-icon", "mpt_event",[[["category","Service"],["label", "ပက်‌ကေ့ချ်"]],[["category","Service"],["label", "Package"]]]),

  //   createFunnelStep("Order Confirmation", "mpt_page",[[["DialogName", "Order Confirmation"]]]),

  //   createFunnelStep("Package Order Success", "mpt_page", [[["DialogName", "Package Order Success"]]])

  // ];

  runFunnelReport(dateStr,homePackIcon,'homePackIcon');

  runFunnelReport(dateStr,nonDXPConversion,'nonDXPConversion');

  // runFunnelReport(dateStr,dxpConversion,'dxpConversion');

  deleteDuplicateRowsByColumns('homePackIcon',[1,6]);

  deleteDuplicateRowsByColumns('nonDXPConversion',[1,6]);

  // deleteDuplicateRowsByColumns('dxpConversion',[1,6]);

  // Pass dateStr to the function

  // runFunnelReport(dateStr);

}

function historical_run(){

  const twoDaysAgo = new Date();

  twoDaysAgo.setDate(twoDaysAgo.getDate() - 1);

  const dateStr = Utilities.formatDate(twoDaysAgo, Session.getScriptTimeZone(), 'yyyy-MM-dd');

  const homePackIcon = [

    createFunnelStep("Home Screen", "mpt_page", [[["pageName", "Home"]]]),

    createFunnelStep("BottomNavi_Service","mpt_event",[[["category","Home"],["action", "Tab bar/ဝန်ဆောင်မှု"]],[["category","Home"],["action", "Tab bar/Services"]]]),

    createFunnelStep("Package-icon", "mpt_event",[[["category","Service"],["label", "ပက်‌ကေ့ချ်"]],[["category","Service"],["label", "Package"]]]),

    createFunnelStep("Package Order Confirmation", "mpt_page",[[["pageName", "Package Order Confirmation"]],[["DialogName", "Order Confirmation"]]]),

    createFunnelStep("Package Order Success", "mpt_page", [[["pageName", "Package Order Success"]],[["DialogName", "Package Order Success"]]])

  ];

  const nonDXPConversion = [

    // createFunnelStep("Home Screen", "mpt_page", [[["pageName", "Home"],["appVersion", "!4.5.7"]],[["pageName", "Home"],["appVersion", "!4.5.8"]],[["pageName", "Home"],["appVersion", "!4.5.9"]],[["pageName", "Home"],["appVersion", "!4.6.0"]],[["pageName", "Home"],["appVersion", "!4.6.1"]]]),

    createFunnelStep([ filter("pageName", "Home"), exclude("appVersion", "4.5.8"), exclude("appVersion", "4.5.7"), exclude("appVersion", "4.5.9"), exclude("appVersion", "4.6.0"), exclude("appVersion", "4.6.1") ]),

    createFunnelStep("BottomNavi_Service","mpt_event",[[["category","Home"],["action", "Tab bar/ဝန်ဆောင်မှု"]],[["category","Home"],["action", "Tab bar/Services"]]]),

    createFunnelStep("Package-icon", "mpt_event",[[["category","Service"],["label", "ပက်‌ကေ့ချ်"]],[["category","Service"],["label", "Package"]]]),

    createFunnelStep("Order Confirmation", "mpt_page",[[["pageName", "Package Order Confirmation"]]]),

    createFunnelStep("Order Confirmation Dialog", "mpt_page",[[["pageName", "Package Order Confirmation Dialog"]]]),

    createFunnelStep("Package Order Success", "mpt_page", [[["pageName", "Package Order Success"]]])

  ];  

  // const dxpConversion = [

  //   createFunnelStep("Home Screen", "mpt_page", [[["pageName", "Home"],["appVersion", "4.5.7"]],[["pageName", "Home"],["appVersion", "4.5.8"]],[["pageName", "Home"],["appVersion", "4.5.9"]],[["pageName", "Home"],["appVersion", "4.6.0"]],[["pageName", "Home"],["appVersion", "4.6.1"]]]),

  //   createFunnelStep("BottomNavi_Service","mpt_event",[[["category","Home"],["action", "Tab bar/ဝန်ဆောင်မှု"]],[["category","Home"],["action", "Tab bar/Services"]]]),

  //   createFunnelStep("Package-icon", "mpt_event",[[["category","Service"],["label", "ပက်‌ကေ့ချ်"]],[["category","Service"],["label", "Package"]]]),

  //   createFunnelStep("Order Confirmation", "mpt_page",[[["DialogName", "Order Confirmation"]]]),

  //   createFunnelStep("Package Order Success", "mpt_page", [[["DialogName", "Package Order Success"]]])

  // ];

  checkAndRepeatForMissingDays('2026-01-01', dateStr,homePackIcon,'homePackIcon');

  checkAndRepeatForMissingDays('2026-01-01', dateStr,nonDXPConversion,'nonDXPConversion');

  // checkAndRepeatForMissingDays('2026-01-01', dateStr,dxpConversion,'dxpConversion');

  // deleteDuplicateRowsByColumns('homeTabEng',[1,6]);

  // deleteDuplicateRowsByColumns('homeTabMyan',[1,6]);

  // deleteDuplicateRowsByColumns('homeTabEngMyan',[1,6]);

}

[Kildere S Irineu]

Analisei o conteúdo do PDF “[Apps-Script] App Version filter is not working”, que é um tópico da comunidade do Google Apps Script relatando um problema ao usar filtros de “does not contain” para App Version em relatórios de GA4 Funnel via Analytics Data API, funcionando na interface do GA4, mas não via API / Apps Script

Gmail - App Version filter is …

.

Abaixo está o diagnóstico técnico e as soluções práticas.

---

✅ Diagnóstico do problema

O problema não está no seu código de Apps Script, mas sim em uma limitação/comportamento da API runFunnelReport (v1alpha) do GA4:

1️⃣ Diferença entre UI do GA4 x Analytics Data API

• A interface do GA4 aceita filtros de texto do tipo:

  • does not contain

• A Analytics Data API (especialmente em Funnel Reports):

  • não suporta corretamente NOT_CONTAINS em algumas dimensões, incluindo appVersion

  • Em muitos casos, o filtro é ignorado silenciosamente (não retorna erro, mas também não filtra)

👉 Esse é exatamente o comportamento descrito no PDF:

“But it can successfully worked in GA4.”

Gmail - App Version filter is …

---

❌ Por que seu código parece “correto”, mas não funciona?

Seu payload está estruturalmente válido, por exemplo:

funnel: {
  steps: funnelSteps
}

E os filtros de etapa (step filters) seguem o padrão da API.

Porém, a API não aplica corretamente operadores negativos (NOT_CONTAINS) em appVersion dentro de funnels.

Isso não é erro de sintaxe nem de autenticação — é limitação funcional da API.

---

✅ Soluções recomendadas (funcionam na prática)

🔹 Opção 1 — Usar EXACT + notExpression

Em vez de vários does not contain, use negação explícita:

{
  notExpression: {
    filter: {
      fieldName: "appVersion",
      stringFilter: {
        matchType: "EXACT",
        value: "4.6.1"
      }
    }
  }
}

👉 Repita esse bloco para cada versão indesejada usando andGroup.

📌 Vantagem:
Funciona melhor do que NOT_CONTAINS na API.

---

🔹 Opção 2 — Filtrar depois no Apps Script (mais confiável)

Buscar os dados sem filtro de versão e filtrar manualmente:

const filteredRows = rows.filter(row => {
  const appVersion = row[APP_VERSION_INDEX];
  return !appVersion.startsWith('4.6.');
});

📌 Vantagens:

• Total controle

• Previsível

• Evita bugs da API

📌 Boa prática:

• Use getValues() e setValues() em batch

• Evite filtros linha a linha em Sheets

---

🔹 Opção 3 — Usar REGEXP (quando disponível)

Em alguns projetos, o seguinte funciona melhor que NOT_CONTAINS:

stringFilter: {
  matchType: "REGEXP",
  value: "^(?!4\\.6\\.).*"
}

⚠️ Atenção:

• Regex nem sempre é suportado em Funnel Reports

• Teste antes em ambiente isolado

---

🔐 Boas práticas adicionais

Segurança

• Use escopos mínimos:

  • https://www.googleapis.com/auth/analytics.readonly

• Evite tokens hardcoded

Performance

• Não faça múltiplas chamadas à API para cada versão

• Prefira 1 chamada + filtro local

Manutenção

• Logue o payload enviado:

Logger.log(JSON.stringify(requestPayload, null, 2));

• Versione o script antes de mudar filtros

---

📚 Referências oficiais

• Analytics Data API – Funnel Reports
  https://developers.google.com/analytics/devguides/reporting/data/v1/funnels

• Apps Script – UrlFetchApp
  https://developers.google.com/apps-script/reference/url-fetch

---

✅ Conclusão

✔ Seu código não está errado
❌ O operador does not contain não funciona corretamente para appVersion em Funnel Reports da API
✅ A solução é usar negação explícita (notExpression) ou filtrar no Apps Script

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/88e7afd5-0bd6-435c-a4d9-5f08f8c7a94bn%40googlegroups.com.