Cómo automatizar en Spreadsheet la monitorización de rankings con Semrush [SIN USAR LA API]

Este artículo sirve como complemento al publicado en Semrush sobre la automatización de la monitorización de rankings sin usar la API.

¡Si te ha gustado la idea se agradece que lo compartas para que llegue a muchísima más gente!

• En Twitter
• En Linkedin
• En Facebook

Aquí tendríamos el código del script correspondiente:

function importCSVFromGmail() {
var threads = GmailApp.search(‘from:”alberto@ingenieroseo.com” subject:”Cliente export”‘);
var message = threads[0].getMessages()[0];
var attachment = message.getAttachments()[0];
var nombrefichero = message.getAttachments()[0].getName();
if (attachment.getContentType() === “application/zip”) {
var csv = Utilities.unzip(attachment);
var sheet = SpreadsheetApp.getActiveSheet();
var csvData = Utilities.parseCsv(csv[0].getDataAsString(), “,”);
readData(csvData);
}
//message.moveToTrash();
}

function readData(csvData) {
var ss = SpreadsheetApp.getActive();
// establecemos la hoja que nos interesa
var sheet = ss.getSheetByName(‘RANKINGS’);
// leemos las keywords que existen en la hoja
var range = sheet.getRange(2,1,sheet.getLastRow()-2);
var data = range.getValues();
// buscamos la ultima columna con fecha e insertamos 1 a continuacion
var column = parseInt(columnaUltimaFecha(sheet));
sheet.insertColumnAfter(column);
// calculamos la fecha de los nuevos datos
//var fecha = new Date(sheet.getRange(1, column).getValue());
//fecha.setDate(fecha.getDate()+7);
//fecha = fecha.getDate() + ‘/’ + (fecha.getMonth()+1);

var nuevaColumna = sheet.getRange(1,column+1);
var anteriorColumna = sheet.getRange(1,column);
var ultimaFila;
// Calculamos la fecha de un día antes de la ejecución
var now = new Date();
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var yesterday = new Date(now.getTime() – MILLIS_PER_DAY);
nuevaColumna.setValue(yesterday);
//nuevaColumna.setValue(fecha);
nuevaColumna.setFontColors(anteriorColumna.getFontColors());
nuevaColumna.setFontFamilies(anteriorColumna.getFontFamilies());
nuevaColumna.setFontWeights(anteriorColumna.getFontWeights());
nuevaColumna.setFontStyles(anteriorColumna.getFontStyles());

for (var filaCSV=1;filaCSV<csvData.length; filaCSV++) {
var keyword = csvData[filaCSV][0].toLowerCase();
if (keyword != ”) {
var nuevaPosicion = csvData[filaCSV][1];
var url = csvData[filaCSV][3];
var trafico = (csvData[filaCSV][7] != ‘n/a’) ? csvData[filaCSV][7] : 5;

for(var filaKW = 0; filaKW<data.length;filaKW++){
if(data[filaKW][0].toLowerCase().trim() == keyword.trim()){
sheet.getRange(filaKW+2,2).setValue(trafico);
sheet.getRange(filaKW+2,column+1).setValue(parseInt(nuevaPosicion) < 100 ? nuevaPosicion : ‘101’ );
sheet.getRange(filaKW+2,column+1).setHorizontalAlignment(“right”);
//diferencia con respecto al 1er día
sheet.getRange(filaKW+2,column+2).setFormulaR1C1(“=R[0]C[-“+(column-1)+”]-R[0]C[-“+(column-(column-1))+”]”);
//diferencia con respecto al día anterior
sheet.getRange(filaKW+2,column+3).setFormulaR1C1(“=R[0]C[-“+(column-(column-3))+”]-R[0]C[-“+(column-(column-2))+”]”);
//url anterior
sheet.getRange(filaKW+2,column+5).setValue(sheet.getRange(filaKW+2,column+4).getValue());
//url
sheet.getRange(filaKW+2,column+4).setValue(url);
}
}
}
}

//keywords no listadas en el archivo pasan a 101
for (i=2;i<=sheet.getLastRow()-2;+i++) {
if (sheet.getRange(i, column+1).getValue() == “”) {
if (sheet.getRange(i,2).getValue() == ”) {
sheet.getRange(i,2).setValue(5);
}
sheet.getRange(i, column+1).setValue(101);
//diferencia con respecto al 1er día
sheet.getRange(i,column+2).setFormulaR1C1(“=R[0]C[-“+(column-1)+”]-R[0]C[-“+(column-(column-1))+”]”);
//diferencia con respecto al día anterior
sheet.getRange(i,column+3).setFormulaR1C1(“=R[0]C[-“+(column-(column-3))+”]-R[0]C[-“+(column-(column-2))+”]”);
//url anterior
sheet.getRange(i,column+5).setValue(sheet.getRange(i,column+4).getValue());
//url
sheet.getRange(i,column+4).setValue(“”);
}
}

//posición media del día
sheet.getRange(sheet.getLastRow(),column+1).setFormulaR1C1(“=SUM(R[-“+(sheet.getLastRow()-2)+”]C[0]:R[-“+(sheet.getLastRow()-(sheet.getLastRow()-2))+”]C[0])/”+(sheet.getLastRow()-3));
//porcentaje diferencia 1er día
sheet.getRange(sheet.getLastRow(),column+2).setFormulaR1C1(“=(R[0]C[-“+(column-1)+”]-R[0]C[-1])/R[0]C[-1]”);
//porcentaje diferencia día anterior
sheet.getRange(sheet.getLastRow(),column+3).setFormulaR1C1(“=(R[0]C[-3]-R[0]C[-2])/R[0]C[-2]”);

//formato condicional escala de colores
escalaDeColores(sheet,column);

sheet.autoResizeColumn(column+1);
}

function columnaUltimaFecha(sheet) {
for(var column=1;column<sheet.getLastColumn();column++){
if(sheet.getRange(1, column).getValue() == ‘Dif. día 1’){
return parseInt(column-1);
}
}
}

function escalaDeColores(sheet,column) {
rule = SpreadsheetApp.newConditionalFormatRule()
.setGradientMaxpointWithValue(“#e67c73″, SpreadsheetApp.InterpolationType.MAX,””)
.setGradientMidpointWithValue(“#ffd666″, SpreadsheetApp.InterpolationType.PERCENTILE,”50”)
.setGradientMinpointWithValue(“#57bb8a”, SpreadsheetApp.InterpolationType.MIN,””)
.setRanges([sheet.getRange(2,3,sheet.getLastRow()-3,column-1)])
.build();
var rules = sheet.getConditionalFormatRules();
rules.length = 0;
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}