Adjust / Cost Integration

const CONFIG = {
  apiToken: "YOUR_API_KEY",
  sheetNameSheet1: "Sheet1",
  baseUrl: "https://ui-api.bidease.com/api/reporting/v1/stats",
  groups: ["day", "operationsystem", "adbundle", "country", "campaignid"],
  filters: {},
  extras: { "group-by-param": "CustomCampaign" },

  // ===== DATES MODE =====
  // 1) Default: useManualDates = false (yesterday)
  // 2) Manual: useManualDates = true
  dateMode: {
    useManualDates: false,
    //useManualDates: true,
    //fromdate: "2026-01-01",
    //todate:   "2026-01-02"
  }
};

function run() {
  console.log("run(): START");

  try {
    const { url } = _buildBideaseUrl_();

    console.log("===== Bidease API REQUEST =====");
    console.log("Method: GET");
    console.log("URL: " + url);
    console.log("================================");

    const response = UrlFetchApp.fetch(url, { method: "GET", muteHttpExceptions: true });
    const status = response.getResponseCode();
    console.log("Response status: " + status);

    if (status !== 200) {
      console.log("Response body (non-200): " + response.getContentText());
      return;
    }

    const text = response.getContentText();
    if (!text) {
      console.log("Empty API response");
      return;
    }

    const table = Utilities.parseCsv(text, ",");
    console.log("Rows parsed: " + (table ? table.length : 0));
    if (!table || table.length < 2) {
      console.log("No data rows");
      return;
    }

    _appendToSheet1_(table);
  } catch (e) {
    console.log("Execution error: " + (e && e.stack ? e.stack : e));
  } finally {
    console.log("run(): END");
  }
}

function _getDateParams_() {
  const fmt = (d) => Utilities.formatDate(d, "UTC", "yyyy-MM-dd");

  const dm = CONFIG.dateMode || {};
  if (dm.useManualDates) {
    const fromdate = String(dm.fromdate || "").trim();
    const todate = String(dm.todate || "").trim();

    if (!fromdate || !todate) {
      throw new Error('Manual dates are enabled, but fromdate/todate are not set in CONFIG.dateMode');
    }

    const re = /^\d{4}-\d{2}-\d{2}$/;
    if (!re.test(fromdate) || !re.test(todate)) {
      throw new Error('fromdate/todate must be in format "YYYY-MM-DD"');
    }

    return { fromdate, todate };
  }

  const now = new Date();

  const todayUTC = new Date(Date.UTC(
    now.getUTCFullYear(),
    now.getUTCMonth(),
    now.getUTCDate()
  ));

  const yesterdayUTC = new Date(todayUTC);
  yesterdayUTC.setUTCDate(todayUTC.getUTCDate() - 1);

  return { fromdate: fmt(yesterdayUTC), todate: fmt(todayUTC) };
}

function _buildBideaseUrl_() {
  const dates = _getDateParams_();

  const params = {
    "api-token": CONFIG.apiToken,
    fromdate: dates.fromdate,
    todate: dates.todate
  };

  const pairs = [];
  _pushParamPairs_(pairs, params);

  (CONFIG.groups || []).forEach((g) => pairs.push(["group", g]));
  _pushParamPairs_(pairs, CONFIG.filters || {});
  _pushParamPairs_(pairs, CONFIG.extras || {});

  const query = pairs
    .map(([k, v]) => `${encodeURIComponent(k)}=${encodeURIComponent(String(v))}`)
    .join("&");

  return { url: `${CONFIG.baseUrl}?${query}` };
}

function _pushParamPairs_(out, obj) {
  Object.keys(obj || {}).forEach((k) => {
    const v = obj[k];
    if (v === null || v === undefined) return;

    if (Array.isArray(v)) {
      v.forEach((item) => {
        if (item === null || item === undefined || item === "") return;
        out.push([k, item]);
      });
      return;
    }

    if (v === "") return;
    out.push([k, v]);
  });
}

function _appendToSheet1_(table) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) throw new Error("Active spreadsheet not found");

  const sheet = ss.getSheetByName(CONFIG.sheetNameSheet1);
  if (!sheet) throw new Error(`Sheet "${CONFIG.sheetNameSheet1}" not found`);

  const header = table[0];
  const idx = _indexMap_(header);

  const required = ["day", "CustomCampaign", "campaignid", "country", "adbundle", "operationsystem", "spend"];
  const missing = required.filter((k) => idx[k] === undefined);
  if (missing.length) {
    console.log("Header row: " + JSON.stringify(header));
    throw new Error("Missing column(s): " + missing.join(", "));
  }

  if (sheet.getLastRow() === 0) {
    sheet.getRange(1, 1, 1, 7).setValues([[
      "date", "campaign_name", "campaign_id", "country", "store_id", "store_type", "ad_spend"
    ]]);
  }

  let total = 0;
  let skippedZero = 0;
  let skippedBadDate = 0;

  const badDateSamples = [];
  const out = [];
  const iso3List = table.slice(1).map(r => r[idx["country"]]);
  const isoMap = _iso3ToIso2ViaRestCountries_(iso3List);

  for (const row of table.slice(1)) {
    total++;

    const spend = _parseNumber_(row[idx["spend"]]);
    if (spend === 0) {
      skippedZero++;
      continue;
    }

    const os = String(row[idx["operationsystem"]] || "").toLowerCase().trim();

    const rawDay = row[idx["day"]];
    const dateObj = _parseDayToDate_(rawDay);
    if (!dateObj) {
      skippedBadDate++;
      if (badDateSamples.length < 5) badDateSamples.push(String(rawDay));
      continue;
    }

    out.push([
      dateObj,
      row[idx["CustomCampaign"]],
      row[idx["campaignid"]],
      _iso3ToIso2_(row[idx["country"]], isoMap),
      row[idx["adbundle"]],
      os === "android" ? "google_play" : os === "ios" ? "app_store" : "",
      spend
    ]);
  }

  console.log("Rows total: " + total);
  console.log("Skipped spend=0: " + skippedZero);
  console.log("Skipped bad date: " + skippedBadDate);
  if (badDateSamples.length) console.log("Bad day samples: " + JSON.stringify(badDateSamples));
  console.log("Rows to append: " + out.length);

  if (!out.length) return;

  const startRow = sheet.getLastRow() + 1;
  sheet.getRange(startRow, 1, out.length, out[0].length).setValues(out);
  sheet.getRange(startRow, 1, out.length, 1).setNumberFormat("yyyy-mm-dd");

  console.log("Appended: " + out.length);
}

function _indexMap_(headerRow) {
  const map = {};
  (headerRow || []).forEach((name, i) => {
    const key = String(name).replace(/^\uFEFF/, "").trim();
    map[key] = i;
  });
  return map;
}

function _parseNumber_(value) {
  if (value === null || value === undefined) return 0;
  const s = String(value).trim();
  if (!s) return 0;
  const cleaned = s.replace(/\s|\u00A0/g, "").replace(",", ".");
  const n = Number(cleaned);
  return Number.isFinite(n) ? n : 0;
}

function _parseDayToDate_(dayValue) {
  if (Object.prototype.toString.call(dayValue) === "[object Date]" && !isNaN(dayValue.getTime())) {
    return new Date(dayValue.getFullYear(), dayValue.getMonth(), dayValue.getDate(), 12, 0, 0);
  }

  const s0 = String(dayValue || "").trim();
  if (!s0) return null;

  const s = s0.replace(/^["']+|["']+$/g, "").trim();

  let m = s.match(/(\d{4})-(\d{2})-(\d{2})/);
  if (m) return new Date(Number(m[1]), Number(m[2]) - 1, Number(m[3]), 12, 0, 0);

  m = s.match(/(\d{2})\/(\d{2})\/(\d{4})/);
  if (m) return new Date(Number(m[3]), Number(m[1]) - 1, Number(m[2]), 12, 0, 0);

  m = s.match(/(\d{4})\/(\d{2})\/(\d{2})/);
  if (m) return new Date(Number(m[1]), Number(m[2]) - 1, Number(m[3]), 12, 0, 0);

  m = s.match(/(\d{2})\.(\d{2})\.(\d{4})/);
  if (m) return new Date(Number(m[3]), Number(m[2]) - 1, Number(m[1]), 12, 0, 0);

  return null;
}

function _iso3ToIso2ViaRestCountries_(iso3List) {
  const cache = CacheService.getScriptCache();

  const uniq = {};
  (iso3List || []).forEach((x) => {
    const k = String(x || "").trim().toUpperCase();
    if (k) uniq[k] = true;
  });
  const codes = Object.keys(uniq);
  if (!codes.length) return {};

  const cached = cache.getAll(codes);
  const map = {};
  const missing = [];

  codes.forEach((c) => {
    const v = cached[c];
    if (v) map[c] = v;
    else missing.push(c);
  });

  if (!missing.length) return map;

  const chunkSize = 50;
  for (let i = 0; i < missing.length; i += chunkSize) {
    const chunk = missing.slice(i, i + chunkSize);

    const url =
      "https://restcountries.com/v3.1/alpha?codes=" +
      encodeURIComponent(chunk.join(",")) +
      "&fields=cca3,cca2";

    try {
      const resp = UrlFetchApp.fetch(url, { method: "GET", muteHttpExceptions: true });
      const status = resp.getResponseCode();

      if (status !== 200) {
        console.log("REST Countries non-200: " + status + " body=" + resp.getContentText());
        continue;
      }

      const data = JSON.parse(resp.getContentText() || "[]");
      if (!Array.isArray(data)) continue;

      const toCache = {};
      data.forEach((item) => {
        const cca3 = String(item && item.cca3 || "").trim().toUpperCase();
        const cca2 = String(item && item.cca2 || "").trim().toUpperCase();
        if (cca3 && cca2) {
          map[cca3] = cca2;
          toCache[cca3] = cca2;
        }
      });

      cache.putAll(toCache, 21600);
    } catch (e) {
      console.log("REST Countries fetch error: " + (e && e.stack ? e.stack : e));
    }
  }

  return map;
}

function _iso3ToIso2_(iso3, isoMap) {
  const key = String(iso3 || "").trim().toUpperCase();
  if (!key) return "";
  return (isoMap && isoMap[key]) ? isoMap[key] : key;
}