Web Vitals - multi-domain (apps script)

index.js

/**
 * Settings
 */
var CrUX_API_KEY = 'AIzaSyBTV0kxLTeSL-vSpKIakbDJrTzfWNhU9c8';
var CRUX_METRICS = ['first_input_delay', 'largest_contentful_paint', 'cumulative_layout_shift'];

var DEVICE_MOBILE = 'PHONE';
var DEVICE_DESKTOP = 'DESKTOP';

// Set to false to production run
var debug = false;

// Do kterych sloupcu tabulky to ukladat, pocinaje cislem…
var firstColMobile = 2;
var firstColDesktop = 6;
var urlCol = 1;
var rowToStart = 2; // <-- **TODO**
var rowToEnd = null;

// Batch options
var batchSize = 5; // <-- **TODO**
var runNextBatchIn = 6 * 60 * 1000 // <-- 6 minut (limit pro Apps Script)

/**
 * Custom menu
 */

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('CrUX')
      .addItem('Získat nová data', 'firstRun')
      .addToUi();
}

/**
 * Code
 */
var CrUXApiUtil = {};
CrUXApiUtil.API_KEY = CrUX_API_KEY;
CrUXApiUtil.API_ENDPOINT = 'https://chromeuxreport.googleapis.com/v1/records:queryRecord?key=' + CrUXApiUtil.API_KEY;
CrUXApiUtil.query = function (requestBody) {
  try {
    var options = {
      method: 'POST',
      contentType: 'application/json',
      payload: JSON.stringify(requestBody)
    }
    var crUXResponse = UrlFetchApp.fetch(CrUXApiUtil.API_ENDPOINT, options);
    return JSON.parse(crUXResponse);
  } catch(e) {
    return {
      error: e
    }
  }
};

function mergeData(range, newData) {
  var oldData = range.getValues();
  var mergedData = newData.map(function(row, i) {
    return row.map(function(col, j) {
      return col === null ? oldData[i][j] : col;
    });
  });
  return mergedData;
}

function setNewValues(range, data, notes) {
  range.setValues(data).setNotes(notes);
}

function setLastRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // Vezmi aktivni tabulku
  var sheet = ss.getSheets()[0];

  rowToEnd = sheet.getLastRow();
}

function getCrUXData(firstRow, lastRow, device) {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // Vezmi aktivni tabulku
  var sheet = ss.getSheets()[0];
  var date = new Date().toLocaleDateString();
  var results = [];
  var notes = [];
  var urls = sheet.getRange(firstRow, urlCol, lastRow - firstRow + 1).getValues().map(function(url) {
    return url[0];
  });

  urls.forEach(function(url) {
    console.log('Getting results for:', url);
    var errors = [];
    var crUXData = CrUXApiUtil.query({
      "origin": url,
      "formFactor": device,
      "metrics": CRUX_METRICS,
    });

    if (!crUXData.error) {
      var LCP = crUXData.record.metrics && crUXData.record.metrics.largest_contentful_paint ? crUXData.record.metrics.largest_contentful_paint.percentiles.p75/1000 : null;
      var FID = crUXData.record.metrics && crUXData.record.metrics.first_input_delay ? crUXData.record.metrics.first_input_delay.percentiles.p75 : null;
      var CLS = crUXData.record.metrics && crUXData.record.metrics.cumulative_layout_shift ? parseFloat(crUXData.record.metrics.cumulative_layout_shift.percentiles.p75) : null;
      if (!LCP && LCP !== 0) errors.push('No data for LCP');
      if (!FID && FID !== 0) errors.push('No data for FID');
      if (!CLS && CLS !== 0) errors.push('No data for CLS');

      results.push([LCP, FID, CLS, LCP || FID || CLS ? date : null]);
      notes.push([null, null, null, errors.length ? 'Last error (' + date + '): ' + errors.join(', ') : null]);
    } else {
      console.log('Fetch error', crUXData.error);
      results.push([null, null, null, null]);
      notes.push([null, null, null, 'Last error (' + date + '): ' + crUXData.error]);
    }
  });

  if (debug) {
    console.log(results);
  } else {
    if (device === DEVICE_MOBILE) {
      var rangeMobile = sheet.getRange(firstRow, firstColMobile, lastRow - firstRow + 1, 4);
      var mobileData = mergeData(rangeMobile, results);
      setNewValues(rangeMobile, mobileData, notes);
    } else {
      var rangeDesktop = sheet.getRange(firstRow, firstColDesktop, lastRow - firstRow + 1, 4);
      var desktopData = mergeData(rangeDesktop, results);
      setNewValues(rangeDesktop, desktopData, notes);
    }
  }
}

function resetAll() {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('ITERATOR', '1');
  ScriptApp.getProjectTriggers().forEach(function(trigger) {
    if (trigger.getHandlerFunction() !== 'firstRun') {
      ScriptApp.deleteTrigger(trigger);
    }
  });
}

function getResults() {
  setLastRow();

  var startTime = new Date().getTime();
  var scriptProperties = PropertiesService.getScriptProperties();
  var iteration = parseInt(scriptProperties.getProperty('ITERATOR') || '1');
  var currentTriggerId = scriptProperties.getProperty('TRIGGER_ID');
  var firstRow = rowToStart + (batchSize * (iteration - 1));
  var lastInIteration = (rowToStart - 1) + (batchSize * iteration);
  var lastRow = lastInIteration < rowToEnd ? lastInIteration : rowToEnd;

  console.log('Get web vitals for mobile: ' + firstRow + '-' + lastRow);
  getCrUXData(firstRow, lastRow, DEVICE_MOBILE);
  console.log('Get web vitals for desktop: ' + firstRow + '-' + lastRow);
  getCrUXData(firstRow, lastRow, DEVICE_DESKTOP);

  if (lastRow < rowToEnd) {
    // Delete project triggers after 10 iterations (if there are too many triggers, it can cause the script to fail)
    if (iteration % 10 === 0) {
      ScriptApp.getProjectTriggers().forEach(function(trigger) {
        if (trigger.getUniqueId() !== currentTriggerId && trigger.getHandlerFunction() !== 'firstRun') {
          ScriptApp.deleteTrigger(trigger);
        }
      });
    }
    // update iterator
    var dateTime = new Date(startTime + runNextBatchIn);
    console.log('Updating iterator to:', iteration + 1, ' Next batch scheduled for:', dateTime);
    scriptProperties.setProperty('ITERATOR', iteration + 1);
    var currentTrigger = ScriptApp.newTrigger('getResults').timeBased().at(dateTime).create();
    scriptProperties.setProperty('TRIGGER_ID', currentTrigger.getUniqueId());
  } else {
    // reset iterator
    console.log('Whole list processed. Reseting iterator and deleting all triggers...');
    resetAll();
    console.log('All done!');
  }
}

function getAllResults() {
  setLastRow();
  console.log('Get web vitals for mobile: ' + rowToStart + '-' + rowToEnd);
  getCrUXData(rowToStart, rowToEnd, DEVICE_MOBILE);
  console.log('Get web vitals for desktop: ' + rowToStart + '-' + rowToEnd);
  getCrUXData(rowToStart, rowToEnd, DEVICE_DESKTOP);
  console.log('All done!');
}

function firstRun() {
  resetAll();
  getAllResults();

  // use getResults to run in batches if getAllResults() will fail
  // getResults();
}

/**
 * Máchal's code starts here ;)
 * ----------------------------
 */ 


/**
 * Returns "rychlé", "zlepšit", "nedostatečné"
 */

function speedStatus(metric_lcp, metric_fid, metric_cls) {
  var value = '';
  if (metric_lcp > 4 || metric_fid > 300 || metric_cls > 0.25) {
    value = 'nedostatečné';
  }
  else if ( (metric_lcp >= 2.5 && metric_lcp <= 4) || (metric_fid >= 100 && metric_fid <= 300) || (metric_cls >= 0.1 && metric_cls <= 0.25) ) {
    value = 'zlepšit';
  }
  else if (metric_lcp < 2.5 && metric_fid < 100 && metric_cls < 0.1) {
    value = 'rychlé';
  }
  else if ((metric_lcp == '' && metric_lcp !== 0)  || (metric_fid == '' && metric_fid !== 0) || (metric_cls == '' && metric_cls !== 0)) {
    value = '';
  }    
  return value;
}

/**
 * Returns CrUX report on Treo.sh
 */

function getTreoLink(url) {
  var clean_url = url.replace(/^https?:\/\//, '').toLowerCase();
  return 'https://treo.sh/sitespeed/'+clean_url+'?metrics=fcp%2Clcp%2Cfid%2Ccls%2Cttfb';
}