This Google Apps Script retrieves data from Google APIs and generates a performance ranking for given domains to a Google Sheets spreadsheet.

individualMetrics.gs

function processTopStoresIndividualMetrics(startRow, endRow){
  var apiKey = "AIzaSyCsnUJ7R8FKNgPwOc8TuPtbkiN4jQWMGHQ";
  var spreadsheetId = "1SWvdwo6m8c3ouW3PUFkbeE8u-NuM0jrUs0Mku88Iv9o"
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheetName = "TOP Stores";
  var sheet = spreadsheet.getSheetByName(sheetName);
  var startRow = startRow || 1;
  var endRow = endRow || 10;
  var urlRange = sheet.getRange("A" + startRow + ":A" + endRow);
  var urlValues = urlRange.getValues();
  var urls = urlValues.flat();
  var results = [];
  var errors = [];
  var apiUrl, response, data;

  var resultsSheetName = "Results";
  var resultsheet = spreadsheet.getSheetByName(resultsSheetName);



  for (var i = 0; i < urls.length; i++){
    var url = "https://www." + urls[i];
    apiUrl = "https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=" + encodeURIComponent(url) + "&strategy=desktop&fields=originLoadingExperience.metrics&key=" + apiKey;
    try{
      response = UrlFetchApp.fetch(apiUrl);
      data = JSON.parse(response.getContentText());
      var currentDate = new Date();
      var formattedDateTime = currentDate.toLocaleString();


      var metrics = data.originLoadingExperience.metrics;
      

      var CUMULATIVE_LAYOUT_SHIFT_SCORE = metrics.CUMULATIVE_LAYOUT_SHIFT_SCORE.percentile;
      var EXPERIMENTAL_INTERACTION_TO_NEXT_PAINT = metrics.EXPERIMENTAL_INTERACTION_TO_NEXT_PAINT.percentile;
      var EXPERIMENTAL_TIME_TO_FIRST_BYTE = metrics.EXPERIMENTAL_TIME_TO_FIRST_BYTE.percentile;
      var FIRST_CONTENTFUL_PAINT_MS = metrics.FIRST_CONTENTFUL_PAINT_MS.percentile;
      var FIRST_INPUT_DELAY_MS = metrics.FIRST_INPUT_DELAY_MS.percentile;
      var INTERACTION_TO_NEXT_PAINT = metrics.INTERACTION_TO_NEXT_PAINT.percentile;
      var LARGEST_CONTENTFUL_PAINT_MS = metrics.LARGEST_CONTENTFUL_PAINT_MS.percentile;
      

      var dateCell = resultsheet.getRange("A" + (startRow + i));
      dateCell.setValue(formattedDateTime);

      var urlCell = resultsheet.getRange("B" + (startRow + i));
      urlCell.setValue(url);

      var clsCell = resultsheet.getRange("C" + (startRow + i));
      clsCell.setValue(CUMULATIVE_LAYOUT_SHIFT_SCORE);

      var einpCell = resultsheet.getRange("D" + (startRow + i));
      einpCell.setValue(EXPERIMENTAL_INTERACTION_TO_NEXT_PAINT);

      var tfbCell = resultsheet.getRange("E" + (startRow + i));
      tfbCell.setValue(EXPERIMENTAL_TIME_TO_FIRST_BYTE);

      var fcpCell = resultsheet.getRange("F" + (startRow + i));
      fcpCell.setValue(FIRST_CONTENTFUL_PAINT_MS);

      var fidCell = resultsheet.getRange("G" + (startRow + i));
      fidCell.setValue(FIRST_INPUT_DELAY_MS);

      var inpCell = resultsheet.getRange("H" + (startRow + i));
      inpCell.setValue(INTERACTION_TO_NEXT_PAINT);

      var lcpCell = resultsheet.getRange("I" + (startRow + i));
      lcpCell.setValue(LARGEST_CONTENTFUL_PAINT_MS);
      
      SpreadsheetApp.flush();
 
    }catch(error){
      errors.push({
        url: url,
        error: error.toString(),
      });
    }
  }
}

metrics.gs

function processRanking(startRow, endRow ){
  var apiKey = "AIzaSyCsnUJ7R8FKNgPwOc8TuPtbkiN4jQWMGHQ";
  var spreadsheetId = "1SWvdwo6m8c3ouW3PUFkbeE8u-NuM0jrUs0Mku88Iv9o"
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheetName = "TOP Stores"
  var sheet = spreadsheet.getSheetByName(sheetName);
  var startRow = startRow || 2;
  var endRow = endRow || 251;
  var urlRange = sheet.getRange("A" + startRow + ":A" + endRow);
  var urlValues = urlRange.getValues();
  var urls = urlValues.flat();
  var errors = [];
  var options = {
    method: "get",
    muteHttpExceptions: true
  };

  function processStrategy(strategy, scoreColumn, dateColumn) {
    for (var i = 0; i < urls.length; i++) {
      try {
        var url = "https://www." + urls[i];
        var apiUrl =
          "https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=" +
          encodeURIComponent(url) +
          "&strategy=" +
          strategy +
          "&fields=lighthouseResult.categories.performance.score&key=" +
          apiKey;

        var response = UrlFetchApp.fetch(apiUrl, options);

        if (response.getResponseCode() == 200) {
          var currentTime = new Date();
          var formattedDateTime = currentTime.toLocaleString();

          var content = response.getContentText();
          var data = JSON.parse(content);
          var performanceScore = data.lighthouseResult.categories.performance
            .score * 100;

          var scoreCell = sheet.getRange(scoreColumn + (startRow + i));
          scoreCell.setValue(performanceScore);

          var dateTimeCell = sheet.getRange(dateColumn + (startRow + i));
          dateTimeCell.setValue(formattedDateTime);

          SpreadsheetApp.flush();
        }
      } catch (error) {
        errors.push({
          url: url,    
          error:
            "Erro no processamento da linha: " +
            (startRow + i) +
            ". Detalhes do erro: " +
            error,
        });
      }
    }
  }
  
  //Here you choose which columns will receive the results
  processStrategy("mobile", "J", "K");
  processStrategy("desktop", "L", "M");
}

views.gs

function writeSimilarWebData(){
  var spreadsheetId = "1SWvdwo6m8c3ouW3PUFkbeE8u-NuM0jrUs0Mku88Iv9o"
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var activeSheet = spreadsheet.getActiveSheet();
  var lastRow = activeSheet.getLastRow();
  var domainColumn = 1; // Column A (1-based index)
  var outputColumn = 2; // Column B (1-based index)
  
  for (var row = 2; row <= lastRow; row++) {
    var domain = activeSheet.getRange(row, domainColumn).getValue();
    var dataResult = similarWeb(domain);

    if (dataResult.data !== null) {
      activeSheet.getRange(row, outputColumn).setValue(dataResult.data);
    } else {
      activeSheet.getRange(row, outputColumn).setValue('N/A');
      Logger.log('Error for domain in row ' + row + ': ' + dataResult.error);
    }
  }
}

function similarWeb(domain){
  var url = 'https://data.similarweb.com/api/v1/data?domain=' + encodeURIComponent(domain);
  var headers = {
    'accept': '/',
    'accept-language': 'en-US,en',
    'cache-control': 'no-cache',
    'content-type': 'application/json',
    'pragma': 'no-cache',
    'sec-fetch-dest': 'empty',
    'sec-fetch-mode': 'cors',
    'sec-fetch-site': 'none',
    'sec-gpc': '1'
  };

  try {
    var options = {
      'method': 'GET',
      'headers': headers,
      'contentType': 'application/json',
      'muteHttpExceptions': true
    };
    var response = UrlFetchApp.fetch(url, options);
    var content = response.getContentText();
    var data = JSON.parse(content);

    var visitsPerMonth = data.EstimatedMonthlyVisits;
    var lastMonthData = null;

    for (var date in visitsPerMonth) {
      lastMonthData = visitsPerMonth[date];
    }

    return { data: lastMonthData, error: null }; 

  } catch(error){
    return { data: null, error: error };

  }

}