Data analysis is the key to any successful digital strategy. To streamline this process, we have developed a Google Apps Script solution that connects your BMS account directly to Google Sheets. In the following steps, you will learn how to configure the tool to exclusively import your campaign metrics. This gives you the autonomy to create custom dashboards and reports quickly and easily.

Step 1: Preparing Google Sheets

1. Open a blank spreadsheet in Google Sheets (or the spreadsheet where you want to generate the data).
2. In the top menu, click on Extensions > Apps Script.
3. A new browser tab will open. This is the code editor where we will install the system.

Step 2: Inserting the Codes

In the code editor, you will need to configure exactly two files in the left sidebar. Follow the instructions and copy the codes below.

File 1: Code.gs (The Engine)

1. Click on the Code.gs file that is created by default in the left sidebar.
2. Delete everything inside it.
3. Copy the code below and paste it there:
   

JavaScript

/**

 * BMS METRICS INTEGRATION - GOOGLE SHEETS

 */

const CONFIG = {

  apiMonUrl: "https://api.mon.bluems.com/v1",

  apiDspUrl: "https://api.dsp.bluems.com/v1",

  sheetData: "Data",

  limitRows: 200000, 

  chunkDays: 31,

  idsPerRequest: 50,

  periodSeconds: 86400,

  pageSizeMetrics: 200,

  cacheTime: 1800

};

function onOpen() {

  SpreadsheetApp.getUi().createMenu('BMS Metrics 🚀')

    .addItem('📋 Open Panel', 'showSidebar')

    .addToUi();

}

function showSidebar() {

  const html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('BMS Metrics').setWidth(350);

  SpreadsheetApp.getUi().showSidebar(html);

}

function checkConfig() {

  const p = PropertiesService.getScriptProperties();

  return (p.getProperty('BMS_API_KEY') && p.getProperty('BMS_ACCOUNT_ID')) ? true : false;

}

function saveSettings(key, id) {

  PropertiesService.getScriptProperties().setProperties({'BMS_API_KEY': key, 'BMS_ACCOUNT_ID': id});

  return true;

}

function getSidebarData() {

  const p = PropertiesService.getScriptProperties();

  const key = p.getProperty('BMS_API_KEY'), accId = p.getProperty('BMS_ACCOUNT_ID');

  if (!key || !accId) throw new Error("Missing credentials.");

  const cache = CacheService.getUserCache().get("BMS_DATA_FINAL");

  if (cache) return JSON.parse(cache);

  const camps = [];

  try {

    const resp = UrlFetchApp.fetch(`${CONFIG.apiDspUrl}/accounts/${accId}/campaigns`, {headers:{"X-Api-Key":key}, muteHttpExceptions:true});

    if(resp.getResponseCode()===200) {

      JSON.parse(resp.getContentText()).values.forEach(c => camps.push({id: c.campaignId||c.id, name: c.name||c.id, tz: c.timezone||"UTC", created: c.createdAt}));

    }

  } catch(e) {}

  let metrics = [];

  try {

    const resp = UrlFetchApp.fetch(`${CONFIG.apiMonUrl}/metrics?pageSize=${CONFIG.pageSizeMetrics}`, {headers:{"X-Api-Key":key}, muteHttpExceptions:true});

    metrics = JSON.parse(resp.getContentText()).values || [];

  } catch(e) {}

  const uniqueMets = Array.from(new Map(metrics.map(m => [m.metricId, m])).values()).sort((a,b) => a.name.localeCompare(b.name));

  const data = { campaigns: camps.sort((a,b) => a.name.localeCompare(b.name)), metrics: uniqueMets };

  try { CacheService.getUserCache().put("BMS_DATA_FINAL", JSON.stringify(data), CONFIG.cacheTime); } catch(e){}

  return data;

}

function processMultiReport(selCamps, selMets, startDateStr) {

  // Saves the selection for the automatic background execution

  PropertiesService.getScriptProperties().setProperty('BMS_LAST_CAMPS', JSON.stringify(selCamps));

  PropertiesService.getScriptProperties().setProperty('BMS_LAST_METS', JSON.stringify(selMets));

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  let sheet = ss.getSheetByName(CONFIG.sheetData);

  if (!sheet) {

    sheet = ss.insertSheet(CONFIG.sheetData);

    sheet.appendRow(["Date", "Campaign"]);

    sheet.setFrozenRows(1);

    sheet.getRange("A:A").setNumberFormat("yyyy-MM-dd");

  }

  const lastCol = sheet.getLastColumn() || 2;

  let headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];

  selMets.forEach(m => {

    let colName = `${m.name} (${m.stat})`;

    if (!headers.includes(colName)) headers.push(colName);

  });

  let dataMap = new Map();

  const lastRow = sheet.getLastRow();

  if (lastRow > 1) {

    const existingData = sheet.getRange(2, 1, lastRow - 1, lastCol).getValues();

    existingData.forEach(row => {

      if (!row[0]) return; 

      let dStr = row[0] instanceof Date ? Utilities.formatDate(row[0], "UTC", "yyyy-MM-dd") : String(row[0]);

      let key = dStr + "||" + row[1];

      while (row.length < headers.length) row.push("");

      dataMap.set(key, row);

    });

  }

  const p = PropertiesService.getScriptProperties();

  const key = p.getProperty('BMS_API_KEY'), accId = p.getProperty('BMS_ACCOUNT_ID');

  let reqDate = new Date(startDateStr);

  reqDate.setHours(0,0,0,0);

  reqDate.setDate(reqDate.getDate() + 1); 

  const now = new Date(); now.setHours(0,0,0,0);

  const campsByTz = {};

  selCamps.forEach(c => { if(!campsByTz[c.tz]) campsByTz[c.tz] = []; campsByTz[c.tz].push(c); });

  for (const [tz, campaigns] of Object.entries(campsByTz)) {

    for (let i = 0; i < campaigns.length; i += CONFIG.idsPerRequest) {

      const chunk = campaigns.slice(i, i + CONFIG.idsPerRequest);

      const ids = chunk.map(c => c.id), map = new Map(chunk.map(c => [c.id, c.name]));

      let current = new Date(reqDate); current.setDate(current.getDate() - 1);

      while (current < now) {

        let end = new Date(current); end.setDate(end.getDate() + CONFIG.chunkDays);

        if (end > now) end = now; if (end <= current) break;

        const results = fetchInParallel(key, accId, selMets, ids, calculateIsoDates(current, end, tz));

        results.forEach((res, idx) => {

          if (!res || !res[0] || !res[0].results) return;

          const met = selMets[idx];

          let colName = `${met.name} (${met.stat})`;

          let colIdx = headers.indexOf(colName);

          res[0].results.forEach(r => {

            let id = r.group && r.group[0] ? (r.group[0].values ? r.group[0].values[0] : r.group[0].value) : null;

            let cName = map.get(id) || id || "-";

            (r.timestamps || []).forEach((t, k) => {

              let dVal, dStr; 

              try { 

                dVal = new Date(Utilities.formatDate(new Date(t), tz, "yyyy-MM-dd") + "T00:00:00"); 

                dStr = Utilities.formatDate(dVal, "UTC", "yyyy-MM-dd");

              } catch(e){ 

                dVal = new Date(t); dStr = String(t);

              }

              let rowKey = dStr + "||" + cName;

              let rowObj = dataMap.get(rowKey);

              if (!rowObj) {

                rowObj = new Array(headers.length).fill("");

                rowObj[0] = dVal; rowObj[1] = cName;

                dataMap.set(rowKey, rowObj);

              }

              while(rowObj.length < headers.length) rowObj.push("");

              rowObj[colIdx] = r.values[k];

            });

          });

        });

        current = new Date(end);

        Utilities.sleep(50);

      }

    }

  }

  let finalData = Array.from(dataMap.values());

  if (finalData.length > 0) {

    finalData.sort((a, b) => new Date(a[0]) - new Date(b[0]));

    finalData.forEach(r => { while(r.length < headers.length) r.push(""); });

    sheet.clearContents(); 

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

    sheet.getRange(2, 1, finalData.length, headers.length).setValues(finalData); 

    sheet.getRange(1, 1, 1, headers.length).setFontWeight("bold").setBackground("#f3f3f3");

  }

  if (sheet.getLastRow() > CONFIG.limitRows) sheet.deleteRows(2, (sheet.getLastRow() - CONFIG.limitRows) + 1000);

  return finalData.length;

}

function fetchInParallel(key, accId, metrics, ids, dates) {

  const reqs = metrics.map(m => ({

    url: `${CONFIG.apiMonUrl}/accounts/${accId}/metrics:query-statistics`,

    method: "POST", headers: { "X-Api-Key": key, "Content-Type": "application/json" },

    payload: JSON.stringify({ "start": dates.start, "end": dates.end, "limit": 10000, "queries": [{ "id": "q1", "metric": { "metricId": m.id, "statistic": m.stat, "period": CONFIG.periodSeconds, "groupBy": ["campaignId"], "where": [{ "name": "campaignId", "values": ids }] } }] }),

    muteHttpExceptions: true

  }));

  return UrlFetchApp.fetchAll(reqs).map(r => r.getResponseCode() === 200 ? JSON.parse(r.getContentText()) : null);

}

function calculateIsoDates(start, end, tz) {

  const f = "yyyy-MM-dd'T'HH:mm:ss.SSSXXX";

  try { return { start: Utilities.formatDate(start, tz, f), end: Utilities.formatDate(end, tz, f) }; } 

  catch (e) { return { start: Utilities.formatDate(start, "UTC", f), end: Utilities.formatDate(end, "UTC", f) }; }

}

// --- AUTOMATIC DAILY UPDATE FUNCTION (TRIGGER) ---

function autoUpdateDaily() {

  const p = PropertiesService.getScriptProperties();

  const campsStr = p.getProperty('BMS_LAST_CAMPS');

  const metsStr = p.getProperty('BMS_LAST_METS');

  if (!campsStr || !metsStr) {

    console.warn("Run the report manually at least once to configure the automation.");

    return;

  }

  const selCamps = JSON.parse(campsStr);

  const selMets = JSON.parse(metsStr);

  // Sets the start date to 5 days ago to ensure retroactive updates are captured

  let d = new Date();

  d.setDate(d.getDate() - 5);

  let startDateStr = Utilities.formatDate(d, Session.getScriptTimeZone(), "yyyy-MM-dd");

  processMultiReport(selCamps, selMets, startDateStr);

}

File 2: Sidebar.html (The Visual Interface)

1. In the left sidebar of the editor, click the "+" button (Add a file) and choose HTML.
2. Name it exactly Sidebar (Google adds the .html extension automatically).
3. Delete the default content, copy the code below, and paste it:

HTML

<!DOCTYPE html>

<html>

<head>

  <base target="_top">

  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">

  <style>

    body { padding: 10px; padding-bottom: 70px; font-family: 'Segoe UI', sans-serif; background: #f8f9fa; }

    .hidden { display: none !important; }

    .top-bar { display: flex; justify-content: space-between; align-items: center; margin-bottom: 10px; }

    .app-title { font-weight: 700; color: #1a73e8; font-size: 15px; }

    .icon-btn { background: none; border: none; cursor: pointer; color: #9aa0a6; font-size: 16px; padding: 5px; }

    .icon-btn:hover { color: #202124; }

    .card { background: #fff; padding: 10px; border-radius: 6px; box-shadow: 0 1px 2px rgba(0,0,0,0.1); margin-bottom: 10px; }

    h3 { margin: 0 0 8px 0; color: #202124; font-size: 13px; font-weight: 600; }

    .list-box { height: 160px; overflow-y: auto; border: 1px solid #dadce0; border-radius: 4px; background: #fff; }

    .row { display: flex; align-items: center; padding: 5px; border-bottom: 1px solid #f1f3f4; font-size: 12px; }

    .row label { flex: 1; cursor: pointer; white-space: nowrap; overflow: hidden; text-overflow: ellipsis; line-height: 1.2; }

    .row input { margin-right: 8px; }

    .met-id { font-size: 10px; color: #5f6368; font-family: monospace; display: block; }

    .stat-sel { font-size: 10px; width: 70px; margin-left: 5px; }

    .input-std { width: 94%; padding: 6px; border: 1px solid #dadce0; border-radius: 4px; font-size: 12px; margin-bottom: 8px; }

    .btn-main { background: #1a73e8; color: #fff; width: 100%; height: 40px; border: none; border-radius: 4px; font-weight: 600; font-size: 13px; cursor: pointer; margin-top: 10px; display: flex; justify-content: center; align-items: center; gap: 8px; }

    .btn-main:hover { background: #1557b0; }

    .btn-main:disabled { background: #dadce0; color: #888; cursor: default; }

    .tools { font-size: 10px; text-align: right; margin-top: 2px; }

    .tools a { color: #1a73e8; cursor: pointer; margin-left: 6px; text-decoration: none; }

    #status { margin-top: 10px; font-size: 12px; text-align: center; color: #5f6368; }

  </style>

</head>

<body>

  <div id="scr-conf" class="hidden">

    <div class="top-bar"><span class="app-title">⚙️ Configuration</span><button id="btnCloseConf" class="icon-btn" onclick="nav('rep')">✕</button></div>

    <div class="card">

      <input type="text" id="accId" class="input-std" placeholder="Account ID">

      <input type="text" id="apiKey" class="input-std" placeholder="API Key">

    </div>

    <button class="btn-main" style="background:#1e8e3e" onclick="save()">✅ Save</button>

  </div>

  <div id="scr-rep" class="hidden">

    <div class="top-bar"><span class="app-title">🚀 Report</span><button class="icon-btn" onclick="nav('conf')">⚙️</button></div>

    <div class="card"><h3>📅 Start Date</h3><input type="date" id="startDt" class="input-std"></div>

    <div class="card"><h3>📢 Campaigns</h3><input type="text" class="input-std" placeholder="🔍 Filter..." onkeyup="filter('lstCamp', this.value)"><div id="lstCamp" class="list-box"></div><div class="tools"><a onclick="viewSel('lstCamp',this)">🌪️ Filter Sel.</a><a onclick="tog('lstCamp',1)">All</a><a onclick="tog('lstCamp',0)">None</a></div></div>

    <div class="card"><h3>📊 Metrics</h3><input type="text" class="input-std" placeholder="🔍 Filter..." onkeyup="filter('lstMet', this.value)"><div id="lstMet" class="list-box"></div><div class="tools"><a onclick="viewSel('lstMet',this)">🌪️ Filter Sel.</a><a onclick="tog('lstMet',1)">All</a><a onclick="tog('lstMet',0)">None</a></div></div>

    <button id="btnRun" class="btn-main" onclick="run()">🚀 Generate Report</button><div id="status"></div>

  </div>

<script>

  window.onload = () => { document.getElementById('startDt').valueAsDate = new Date(); google.script.run.withSuccessHandler(init).checkConfig(); };

  function init(h) { if(h) { nav('rep'); google.script.run.withSuccessHandler(render).getSidebarData(); } else { nav('conf'); } }

  function nav(s) { document.getElementById('scr-conf').classList.toggle('hidden', s!=='conf'); document.getElementById('scr-rep').classList.toggle('hidden', s!=='rep'); }

  function save() { const k=document.getElementById('apiKey').value, i=document.getElementById('accId').value; google.script.run.withSuccessHandler(()=>{nav('rep');google.script.run.withSuccessHandler(render).getSidebarData();}).saveSettings(k,i); }

  function render(d) {

    let hC="", hM="";

    d.campaigns.forEach(c => hC += `<div class="row"><input type="checkbox" value="${c.id}" n="c" tz="${c.tz}" cr="${c.created}"><label>${c.name}</label></div>`);

    d.metrics.forEach(m => {

      let def = m.statistics.includes('count') ? 'count' : (m.statistics.includes('sum') ? 'sum' : 'avg');

      let ops = m.statistics.map(s => `<option value="${s}" ${s==def?'selected':''}>${s}</option>`).join('');

      hM += `<div class="row"><input type="checkbox" value="${m.metricId}" n="m"><label><span style="font-weight:600">${m.name}</span><span class="met-id">${m.metricId}</span></label><select class="stat-sel">${ops}</select></div>`;

    });

    document.getElementById('lstCamp').innerHTML = hC; document.getElementById('lstMet').innerHTML = hM;

  }

  function run() {

    const dt=document.getElementById('startDt').value;

    const cs=Array.from(document.querySelectorAll('input[n="c"]:checked')).map(e=>({id:e.value, name:e.nextSibling.innerText, tz:e.getAttribute('tz'), created:e.getAttribute('cr')}));

    const ms=Array.from(document.querySelectorAll('input[n="m"]:checked')).map(e=>({id:e.value, name:e.nextSibling.querySelector('span').innerText, stat:e.parentNode.querySelector('select').value}));

    if(!cs.length || !ms.length) return alert("Select campaigns and metrics!");

    const btn=document.getElementById('btnRun'), st=document.getElementById('status');

    btn.disabled=true; btn.innerText="⏳ Processing...";

    st.innerHTML = `Organizing metrics into columns...`;

    google.script.run

      .withSuccessHandler(n=>{

        btn.disabled=false; 

        btn.innerText="🚀 Generate Report"; 

        st.innerHTML=`<span style="color:green; font-weight:bold;">✅ Spreadsheet Updated!</span>`;

      })

      .withFailureHandler(e=>{

        btn.disabled=false; 

        btn.innerText="🚀 Generate Report"; 

        st.innerHTML=`<span style='color:red'>Error: ${e.message}</span>`;

      })

      .processMultiReport(cs,ms,dt);

  }

  function filter(id,v) { Array.from(document.getElementById(id).children).forEach(r=>r.style.display=r.innerText.toLowerCase().includes(v.toLowerCase())?'flex':'none'); }

  function tog(id,s) { Array.from(document.getElementById(id).children).forEach(r=>{if(r.style.display!=='none') r.querySelector('input').checked=!!s;}); }

  function viewSel(id,el) {

    const active=el.getAttribute('on')==='1';

    Array.from(document.getElementById(id).children).forEach(r => r.style.display=active?'flex':(r.querySelector('input').checked?'flex':'none'));

    el.innerText=active?"🌪️ Filter Sel.":"🔙 View All"; el.setAttribute('on',active?'0':'1');

  }

</script>

</body>

</html>

Attention: Click the Floppy Disk icon at the top of the screen to save the project after pasting both codes.

Step 3: Google Permissions

1. Go back to your Google Sheets tab and refresh the page (press F5).
2. Wait a few seconds. You will see a new menu called "BMS Metrics 🚀" appear next to the "Help" menu.
3. Click on BMS Metrics 🚀 > 📋 Open Panel.
4. Since it is the first time running the script, Google will ask for Security Authorization.
5. • Click Continue.
   • Choose your Google account.
   • Click Advanced (if a warning appears) and then Go to project.
   • Click Allow.

Step 4: Configuring your Credentials

1. With permissions granted, click again on BMS Metrics 🚀 > 📋 Open Panel.
2. A side window will open on the right asking for your credentials.
3. Fill in your BMS Account ID and API Key.
4. Click ✅ Save. The main panel will load shortly after.

Step 5: Generating your First Report

1. In the panel, choose the Start Date (e.g., beginning of the month).
2. In the Campaigns section, check the boxes for the campaigns you want to analyze.
3. In the Metrics section, check the boxes for what you want to extract (e.g., Clicks, Impressions, CTR) and verify if the statistic next to it (sum, avg) is correct for your needs.
4. Click 🚀 Generate Report.
5. Wait for the processing. The script will create (or update) a tab called "Data", organizing each metric into a column intelligently and ensuring there are no duplicate entries.