Parsing XML using Google Apps Script

Question

I want to ask about the looping not working as per my expectation in Google spreadsheet. When i run with Logger.log it's working, but it show the same value on the range A2:A20 and B2:B20, please advice how to make it work.

This is the XML file that i want to parsing on the Google spreadsheet: http://mong.myee.web.id/RPX/getRevenue.php?track_from=2021-04-01&track_to=2021-05-20

function parseXml() {
   
   //Define gsheet output information
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName('Sheet1');
 
   //Set variables to data from server
   var Url = 'http://mong.myee.web.id/RPX/getRevenue.php?track_from=2021-04-01&track_to=2021-05-20';
   var xml = UrlFetchApp.fetch(Url).getContentText();
   
   var document = XmlService.parse(xml);
   var root = document.getRootElement();  
   
   var entry = root.getChildren('DATA');
     for (var i = 0; i < entry.length; i++) {
      var elements1 = entry[i].getChild('AWB').getText();
      var elements2 = entry[i].getChild('SERVICE_TYPE_ID').getText();
      
      sheet.getRange('A2:A20').setValue(root);
      sheet.getRange('B2:B20').setValue(elements2);

      Logger.log(elements1);
      Logger.log(elements2);    
     }

Rubén · Accepted Answer · 2021-05-27T15:30:06.143

Easy fix

Instead of getRange('A2:A20') and getRange('B2:B20') use getRange(i + 2,1) and getRange(i + 2,1) respectively:

function parseXml() {
   
   //Define gsheet output information
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName('Sheet1');
 
   //Set variables to data from server
   var Url = 'http://mong.myee.web.id/RPX/getRevenue.php?track_from=2021-04-01&track_to=2021-05-20';
   var xml = UrlFetchApp.fetch(Url).getContentText();
   
   var document = XmlService.parse(xml);
   var root = document.getRootElement();  
   
   var entry = root.getChildren('DATA');
     for (var i = 0; i < entry.length; i++) {
      var elements1 = entry[i].getChild('AWB').getText();
      var elements2 = entry[i].getChild('SERVICE_TYPE_ID').getText();
      
      sheet.getRange(i + 2,1).setValue(elements1);
      sheet.getRange(i + 2,2).setValue(elements2);

      Logger.log(elements1);
      Logger.log(elements2);    
     }

Notes:

Using setValue on a range having multiple cells, will add the same value to all the cells, instead add the value to one cell at a time.
The easy fix and previous note are included to help the OP to understand how setValue works.
In order to write efficiente code, avoid to use SpreadsheetApp methods inside a loop whenever is possible. Instead assign the values to an Array and after the loop finishes add all the values at ones by using setValues

Use batch operations | Best Practices | Google Apps Scripts

Hi Ruben, thanks for your input and suggestion, appreciate it. Since im new in GAS, need some example for avoid to use SpreadsheetApp methods in loop and assign the values to an Array and after the loop finishes add all the values at ones by using setValue. — Caine0027, May 27 '21 at 07:03
@Caine0027 I added some links to my answer. I hope that you will find them helpful. — Rubén, May 27 '21 at 15:33

Parsing XML using Google Apps Script

1 Answers1