在GoogleApps腳本中將XML決議為Google電子表格

我需要將 XML 檔案決議為 Google 電子表格。我需要每一行“行”中的所有資料。每個 URL 的所有值都應在電子表格中擁有自己的行。

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>

我從這個函式開始并得到了一個值（耶！）

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 函式將值（答案）寫入 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  ;
    }

我不知道如何從一行中收集所有值并將它們保存到spreadhseet。

輸出電子表格示例：

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

還有一件事讓我很生氣：據我所知，我需要將 URL 轉換為字串。

uj5u.com熱心網友回復：

Apps 腳本有一個

參考：

• XML 服務
• 獲取范圍（）
• 設定值（）

uj5u.com熱心網友回復：

神圣的弗拉克，丹尼爾。我把一些腳本放在一起，它給了我我需要的東西。

Google Apps 腳本：SISTRIX API 呼叫 page.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)
      }
}

標籤：谷歌应用脚​​本 谷歌表格 xml 解析

上一篇：即使沒有資料，GoogleSheetsApps腳本也會獲取整個作業表的范圍

下一篇：陣列未填充列