Parse XML to Google Spreadsheet in google Apps Script-CodePudding

I need to parse a XML file to Google Spreadsheet. I need all the data from each row "row". Every URL should have its own row in spreadsheet for all its values.

XML File, example:

<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>

I started with this function and got one value back (yay!)

for (var i = 0; i < items.length; i  ) {
    if(items[i].getName() == 'answer'){
      var answer = items[i].getChildren();
      return answer[0].getAttribute('visindex').getValue();
    }
  }

Tis function writes the value (answer) to spreadhseet

     var seoValue = getSeoValue(apikey, seoMetric, keyword, country);
      outputSheet.getRange(outputLastRow, 6   i ).setValue(seoValue/1); //aktuell nur 1 outputwert 
    }
    // increase the last output row by one
    outputLastRow  ;
    }

I dont knwo how to collect all the values from a row and save them to spreadhseet.

Output spreadhsheet example:

INPUT - (excerpt)
<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"/>

OUTPUT - Row A1 | B1 | C1 | D1 
values row-1 -> URL-1-value | top-10-value-1 | top-100-value-1 | visindex-value-1
values row-2 -> URL-2-value | top-10-value-2 | top-100-value-2 | visindex-value-2

And one more thing that kills me: as far as I understand, I need to convert the URL to a string.

CodePudding user response：

Apps Script has an

References:

CodePudding user response：

Holy frak that worked Daniel. I put some scripts together and it gives me what I need.

Google Apps Script: SISTRIX API Call page.urls. Its crap but output is okay.

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)
      }
}