Google Apps Script (GAS) - Check Position & URL with Sistrix API in Google Spreadsheet

check_position_sistrix_api.js

function check_position_sistrix_api() {

  const sistrix_key = 'XXX';
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('check position with sistrix');

  // Werte (Domain, Device) werden eingelesen
  let domain = sheet.getRange('H7').getValue();
  let device = sheet.getRange('H8').getValue();
  
  // Falls das Device 'Mobil' ist, füge den entsprechenden String der Variablen hinzu
  let device_state;
  if (device == 'Mobil') {
    device_state = '&mobile=true';
  } else {
    device_state = '';
  }
  
  // Update-Prozess wird kommuniziert
    sheet.getRange('G1').setValue("Liste wird aktualisiert...");
    sheet.getRange('G1').setFontColor("#e84118");
  
  // Letzte Reihe wird identifiziert
    let dataRange = sheet.getDataRange();  
    let last_row = dataRange.getLastRow();
  
  // Keywords werden in ein Array geschrieben
    const keyword_liste_obj = sheet.getRange('A2:A'+ last_row).getValues();
    const keyword_list_tmp = keyword_liste_obj.map(function(e){ return e[0]; });
    const keyword_list = keyword_list_tmp.filter(function(el) { return el; });
  
  Logger.log('keyword_list: '+keyword_list);
  
  // Anzahl der Keywords wird gezählt
    let last_row_real = keyword_list.length + 1;
  
  // Domain-Spalte wird ausgewählt
    let column_url = sheet.getRange('B2:B'+ last_row_real);
  
  // Erhalte die Werte für Row, Column der aktiven Range 
    let numRows = column_url.getNumRows();
    let numCols = column_url.getNumColumns();
  
  // Schreibe das ausgewählte Device in die Tabelle
    let column_device = column_url.offset(0,2);
    column_device.setValue(device);
    
  // Schreibe die ausgewählte Domain in die Tabelle
    let column_domain = column_url.offset(0,3);
    column_domain.setValue(domain);
  
  // Lese alle Daten aus dem Reiter 'database'
    const sheet_db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('database');
    let range_db = sheet_db.getDataRange();
    let values_db = range_db.getValues();
  
  // Erstelle vier leere Arrays, Deklariere 2 neue Variablen
    let values_urls = [];
    let values_pos = [];
    
    let url_list = [];
    let pos_list = [];
    
    let column_pos;
    let count = 0;

  // Durchlaufe die aktive Range und schreibe am Schluss alles in ein Array, welches anschließend in die Zellen geschrieben wird
  for (let i = 1; i <= numRows; i++) {
      
    let db_check = false;
    
    for (let a = 1; a < values_db.length; a++) {
      if (
        keyword_list[count] == values_db[a][0] &&
        device == values_db[a][3] &&
        domain == values_db[a][4]
      ) {
        url_list.push([values_db[a][1]]);
        pos_list.push([values_db[a][2]]);
        db_check = true;
        
        Logger.log("In DB gefunden: " + keyword_list[count] + ", " + device + ", " + domain);
        break;
      }
    }
    
    if (db_check != true) {
      
      let url = 'https://api.sistrix.com/keyword.seo?api_key=' + sistrix_key + '&domain=' + domain + '&date=now&kw='+ keyword_list[count] + device_state;      
      
      try {
        
        // Lade den Inhalt der XML
        let xml = UrlFetchApp.fetch(url).getContentText();
        
        Logger.log("Sistrix API (URL) verwendet: " + url);
        
        // Parse das XML
        let document = XmlService.parse(xml);
        
        Logger.log(document);
        
        // Wähle das Node "result"
        let xml_result = document.getRootElement().getChild("answer").getChild("result");
        
        // Extrahiere die Wert aus den Attributen
        let res_url = xml_result.getAttribute('url').getValue();
        let res_pos = xml_result.getAttribute('position').getValue();
        
        // Füge die Werte den entsprechenden Arrays hinzu
        url_list.push([res_url]);
        pos_list.push([res_pos]);
        
      } catch (e) {
        
        // Falls nicht das angegebene Node gefunden wurde (result), wird ein Fehler geworfen (= kein Ergbnis von Sistrix zurückgegeben) => füge "-" den Arrays hinzu
        url_list.push(["-"]);
        pos_list.push(["-"]);
        
        Logger.log(e);
        
      }
      
      // Pausiere 0,3 Sekunden, um kein Rate-Limit bei der Sistrix-API zu verursachen (https://www.sistrix.de/api/) 
      Utilities.sleep(300);
      
    }
    
    // Zähle den Counter für das Arrays der Keyword-Liste (keyword_list) hoch
    count++;
    
  }
  
  // Schreibe die Werte der Arrays in die Tabelle
    column_url.setValues(url_list);
    
    column_pos = column_url.offset(0,1);
    column_pos.setValues(pos_list);
  
  // Setzte das aktuelle Datum als Zeitstempel
    sheet.getRange('G1').setValue("Aktualisiert um " + Utilities.formatDate(new Date(), "GMT+2", "HH:mm 'Uhr, 'dd.MM.yyyy"));
    sheet.getRange('G1').setFontColor("#000000");
 
  // Kopiere die Tabelle in den Reiter "database"
    copy_to_database(last_row_real);
  
}


function copy_to_database(last_row_real) {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('check position with sistrix');
  const source = sheet.getRange('A2:E'+last_row_real);
  
  const sheet_db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('database');
  let sheet_db_last_row = sheet_db.getDataRange().getLastRow();
  let sheet_db_last_row_real = sheet_db_last_row + 1;
  source.copyTo(sheet_db.getRange('A'+sheet_db_last_row_real), {contentsOnly: true});

  removeDuplicates(sheet_db);
  
}


// === Entferne Duplikate ===

function removeDuplicates(sheet) {
  let data = sheet.getDataRange().getValues();
  let newData = [];
  for (let i in data) {
    let row = data[i];
    let duplicate = false;
    for (let j in newData) {
      if (row.join() == newData[j].join()) {
        duplicate = true;
      }
    }
    if (!duplicate) {
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}


// === Lösche die Tabelle und füge ein Keyword hinzu (per Button) ===

function delete_data() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('check position with sistrix');
  let dataRange = sheet.getDataRange();  
  let last_row = dataRange.getLastRow();
  sheet.getRange('A2:E'+last_row).clearContent();
  if (last_row > 100) {
    let row_count_to_delete = last_row-100;
    Logger.log(row_count_to_delete)
    sheet.deleteRows(100, row_count_to_delete);
  }
  sheet.getRange('G1').clearContent();
  sheet.getRange('A2').setValue("computerbild");
 
}

function delete_db_once_a_week() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('database');
  sheet.getRange('A2:F10000').clearContent();
 
}




// https://api.sistrix.com/keyword.seo?api_key=${api_key}&domain=${brand}.de&date=now&kw=${keyword}&mobile=${device_state}