Разобрать XML в таблицу Google в скрипте Google Apps

Apps Script имеет XML-сервис, который можно использовать для анализа данных. Вот как вы можете это сделать на основе одного из примеров. Вы можете просто вставить его в проект скрипта приложений нового листа, чтобы протестировать и изменить его по своему усмотрению.

function xmlParser() {

  //input should be your xml file as text
  let xml = '<response><method>domain.urls</method><answer><row url = "https://www.example.com/1" top10 = "3048" top100 = "4490" visindex = "9.1068505804717"/><row url = "https://www.example.com/2" top10 = "2633" top100 = "2720" visindex = "8.6659210425021"/><row url = "https://www.example.com/3" top10 = "875" top100 = "964" visindex = "2.7381900000597"/></answer><credits used = "4"/></response>';
  let document = XmlService.parse(xml); //have the XML service parse the document
  let root = document.getRootElement(); //get the root element of the document
  let answers = root.getChild("answer").getChildren("row"); //gets the 'answer' node, and a list of its subnodes, note that we use getChildren() to get them all in an array

  //now the answers array contains each <row> element with all its attributes

  const list = [] //we create an array that will hold the data

  answers.forEach(function (row) {

  //forEach function that iterates through all the row nodes and uses
  //getAttribute() to get their values based on the names we know already
  //we push each element to our list array

    list.push([row.getAttribute("url").getValue(), row.getAttribute("top10").getValue(), row.getAttribute("top100").getValue(), row.getAttribute("visindex").getValue()])
  }
  )
  writeToSheet(list) // after the array is populated you can call another function to paste in the Sheet
}

function writeToSheet(list) {

  //first set a range where you will paste the data. You have to define the length with the input array
  //the first two parameters are "1, 1" for row 1, column 1, but you can change this depending on your needs.

  let range = SpreadsheetApp.getActiveSheet().getRange(1, 1, list.length, list[0].length)

  //once we have the array set you can just call setValues() on it which pastes the array on its own

  range.setValues(list)

}

Вывод выглядит следующим образом:

Рекомендации:

• XML-сервис
• получитьдиапазон()
• установить значения ()

Черт возьми, это сработало, Дэниел. Я собрал несколько сценариев, и это дало мне то, что мне нужно.

Сценарий Google Apps: страница вызова API SISTRIX.urls. Это дерьмо, но вывод в порядке.

function getData() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var inputSheet = spreadSheet.getSheets()[0];
  var outputSheet = spreadSheet.getSheets()[1];
  // get the last non-empty row number in the input sheet 
  var inputLastRow = inputSheet.getLastRow();
  // get the first empty row number in the output sheet
  var outputLastRow = outputSheet.getLastRow() + 1;
  // get the api key from the input sheet
  var apikey = inputSheet.getRange('A2').getValue();
  
  //var week = getWeek();

  // get the input for queries
  var inputs = inputSheet.getRange('A11:E' + inputLastRow).getValues();

  // specify the SISTRIX KPIs for the client and the competitor(s) 
  
  var clientSeoMetrics = inputSheet.getRange('A5').getValue().split(',');
  // loop over rows in the input
  for (var row = 0; row < inputLastRow - 10; row++) {

    // specify inputs - which column means what  
    //var keyword = inputs[row][0].toLowerCase();
    //var country = inputs[row][2].toLowerCase();
    var domain = inputs[row][0].toLowerCase();
    var limit = inputs[row][1];

        //write the basic information to output   
        //outputSheet.getRange('A'+outputLastRow).setValue(keyword); 
        //B Suchvolumen
        //outputSheet.getRange('C'+outputLastRow).setValue(country.toUpperCase());
        //D wird Search Intent
        //outputSheet.getRange('E'+outputLastRow).setValue(week); 
    
    // check if competition or client and take proper KPIs 
    var seoMetrics;    
        seoMetrics = clientSeoMetrics; //eigenltich unnoetig - evtl. fuer intent sinnvoll (if intent dann)
        
    // loop over seometrics - falls weitere Metriken
    
    for (var i = 0; i < seoMetrics.length; i++) {
      var seoMetric = seoMetrics[i];
      if (seoMetric == ""){
        break;
    }
      // run seoMetric query 
      //var seoValue = getSeoValue(apikey, seoMetric, domain, limit);
      //outputSheet.getRange(outputLastRow, 5 + i ).setValue(seoValue/1); //aktuell nur 1 outputwert 
    
      //var seoValue = getSeoValue(apikey, seoMetric, domain, limit);
      //outputSheet.getRange(outputLastRow, 5 + i ).setValue(seoValue/1); //aktuell nur 1 outputwert 
      var seoValue = [] ;
      var seoValue = getSeoValue(apikey, seoMetric, domain, limit);
    
    }
    // increase the last output row by one
    outputLastRow++;
    }
    

function getSeoValue(apikey, seoMetric, domain, limit){
 
var url = "https://api.sistrix.com/"+seoMetric+"?domain = "+domain+"&api-key = "+apikey+"&country=de&limit = "+limit;
    
    var xml = UrlFetchApp.fetch(url).getContentText();  
    var document = XmlService.parse(xml);

    var root = document.getRootElement(); //get the root element of the document
    var answers = root.getChild("answer").getChildren("row"); //gets the 'answer' node, and a list of its subnodes, note that we use getChildren() to get them all in an array

  //now the answers array contains each <row> element with all its attributes

  const list = [] //we create an array that will hold the data

  answers.forEach(function (row) {

  //forEach function that iterates through all the row nodes and uses
  //getAttribute() to get their values based on the names we know already
  //we push each element to our list array

    list.push([row.getAttribute("url").getValue(), row.getAttribute("top10").getValue(), row.getAttribute("top100").getValue(), row.getAttribute("visindex").getValue()])
  }
  )
  writeToSheet(list) // after the array is populated you can call another function to paste in the Sheet
}

function writeToSheet(list) {
          
        //let range = SpreadsheetApp.getActiveSheet().getRange(1, 1, list.length, list[0].length)
            //range.setValues(list)
            //outputSheet.getRange(outputLastRow, 5 + i ).setValue(list/1); //aktuell nur 1 outputwert 
        let range = outputSheet.getRange(outputLastRow, 1, list.length, list[0].length)
        range.setValues(list)
      }
}