CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user6079228
user6079228

Reputation: 97

Update and insert data instead of complete replacement

I use the code below to retrieve a XML and write the data to Sheet2. But when i run the function again it loads the entire xml again into the sheet.

What i want to achieve, but don't know how:

1. Get de XML and compare it with the data already in Sheet2, based on 2 colums: stationID & stationTypeID

2. When the two columns match, update the entire row. When the columns don't match, insert the new row on top.

  function loadOutposts(){
  var outposts= new Array();
  var url = "https://api.eveonline.com/eve/ConquerableStationList.xml.aspx";
  var parameters = {method : "get", payload : ""};
  var xmlFeed = UrlFetchApp.fetch(url, parameters).getContentText();
  var xml = XmlService.parse(xmlFeed);
  if(xml) {
    var rows=xml.getRootElement().getChild("result").getChild("rowset").getChildren("row");
    for(var i = 0; i < rows.length; i++) {
      outpost=[rows[i].getAttribute("stationID").getValue(),
                 rows[i].getAttribute("stationName").getValue(),
                 rows[i].getAttribute("stationTypeID").getValue(),
                 rows[i].getAttribute("solarSystemID").getValue(),
                 rows[i].getAttribute("corporationID").getValue(),
                 rows[i].getAttribute("corporationName").getValue()
                 ]
      outposts.push(outpost);
    }
  }
  //return outposts;
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2').getRange(1,1,outposts.length,outposts[0].length).setValues(outposts);
};

Thx for the help!

Upvotes: 0

Views: 70

Answers (1)

Tanaike
Tanaike

Reputation: 201643

How about this modification?

Flow :

  1. Data retrieved by UrlFetchApp.fetch() is compared to the existing data on Spreadsheet.
  2. When the columns 1 and 2 for them are same, the existing data is updated.
  3. When the data retrieved from URL is not included in the existing data, the data is inserted to the top of existing data. This existing data becomes new data.
  4. Then, the data on Spreadsheet is updated by new data.

Modified script :

function loadOutposts(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');

  var outposts= new Array();
  var url = "https://api.eveonline.com/eve/ConquerableStationList.xml.aspx";
  var parameters = {method : "get", payload : ""};
  var xmlFeed = UrlFetchApp.fetch(url, parameters).getContentText();
  var xml = XmlService.parse(xmlFeed);
  if (xml) {
    var rows=xml.getRootElement().getChild("result").getChild("rowset").getChildren("row");
    for(var i = 0; i < rows.length; i++) {
      outpost = [
        rows[i].getAttribute("stationID").getValue(),
        rows[i].getAttribute("stationName").getValue(),
        rows[i].getAttribute("stationTypeID").getValue(),
        rows[i].getAttribute("solarSystemID").getValue(),
        rows[i].getAttribute("corporationID").getValue(),
        rows[i].getAttribute("corporationName").getValue()
      ]
      outposts.push(outpost);
    }

    // ----- Added script
    if (ss.getLastRow() > 0) {
      var currentdata = ss.getRange(2, 1, ss.getLastRow(), ss.getLastColumn()).getValues(); // Updated
      currentdata.forEach(function(e1, i1){
        var temp = [];
        outposts.forEach(function(e2, i2){
          if (e1[0] == e2[0] && e1[1] == e2[1]) {
            currentdata[i1] = e2;
            temp.push(i2);
          }
        });
        for (var i in temp) {
          outposts.splice(temp[i], 1);
        }
      });
      Array.prototype.push.apply(outposts, currentdata);
    }
    // -----
  }
  ss.getRange(2,1,outposts.length,outposts[0].length).setValues(outposts);  // Updated
};

If I misunderstand your question, I'm sorry.

Upvotes: 1

Related Questions