Skip to content

Instantly share code, notes, and snippets.

@viniciustrr
Created August 2, 2023 14:20
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save viniciustrr/a71e72910e8f8b1ceda7f0b1ec6c1403 to your computer and use it in GitHub Desktop.
This Google Apps Script retrieves data from Google APIs and generates a performance ranking for given domains to a Google Sheets spreadsheet.
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(),
});
}
}
}
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");
}
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 };
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment