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]);

}