0

So I was given a good direction from a previous post, however there are a few flaws that somehow made it stop working.

Here is the original goal: Have a google sheet that refreshes the rss link and collects title,url and time posted. Then, I want the sheet to hold on to previous set of information when it updates.

I have tried using this method below however it stopped working and plus The second page never collected the url. I tried adding this to see if this would copy it over however no luck. So if anyone has a good method of an updating and saving RSS Feed system, I would love to know how to do it.

function rssfeed() {
  var ss = SpreadsheetApp.getActive();
  ss.getRange('Import RSS!A1').setFormula('=IMPORTFEED("https://retail-insider.com/feed/","items Title",False,100)');
  ss.getRange('Import RSS!B1:B').copyTo(ss.getRange('History RSS!A1:A'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  ss.getRange('Import RSS!A1').clear({contentsOnly: true, skipFilteredRows: true}); 
  ss.getRange('Import RSS!B1').setFormula('=IMPORTFEED("https://retail-insider.com/feed/","items URL",False,100)');
  ss.getRange('Import RSS!B1:B').copyTo(ss.getRange('History RSS!A1:A'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  ss.getRange('Import RSS!B1').clear({contentsOnly: true, skipFilteredRows: true}); 
}
}

enter image description here

0

Consider this variation on your existing code.

The script is in three parts:

  • buildrssfeed(): Similar to your existing script; create the feed from the target site and save to "Import RSS"
  • copyrssfeed(): copy only the required columns of feed data from "Import RSS" to "History RSS"
  • removeduplicates(): delete duplicate entries from "History RSS"

The scripts were written as separate units in order to demonstrate the process involved at each stage. However they could be combined or daisy-chained if preferred. removeduplicates() could also be run from a menu as/when required.

One of the weaknesses in your existing approach is that rssfeed could/would return existing data as well as new data. When this is copied to "History RSS" there may/will be many duplicate entries. To resolve this, removeduplicates deletes the duplicates from "History RSS".

Processing sequence/triggers

  • A time-driven trigger for buildrssfeed() as already proposed.

/**
 * insert `IMPORTFEED` formula and update feed data.
 */
function buildrssfeed() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  
  // build the sheet names
  var importname = "Import RSS1";
  var importsheet = ss.getSheetByName(importname);
  
  // clear any existing data
  importsheet.clear();

  // insert the IMPORTFEED formula
  importsheet.getRange("A1").setFormula('=IMPORTFEED("https://retail-insider.com/feed/","items",True,100)');

  // update the spreadsheet
  SpreadsheetApp.flush();

  copyrssfeed()
  removeDuplicates();

}


/**
 * copy feed data to History
 */
function copyrssfeed() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()

  // build the sheet names
  var importname = "Import RSS1";
  var importsheet = ss.getSheetByName(importname);
  var historyname = "History RSS1";
  var historysheet = ss.getSheetByName(historyname);

  // get last row information 
  var importLR = importsheet.getLastRow();
  var historyLR = historysheet.getLastRow();
  
  // get the RSS data from import RSS
  var importdata = importsheet.getDataRange().getValues();

  // create an array to collect the data
  var historydata = [];

  for (var i = 0;i<importLR-1;i++){
    var newImportRow=[]; // temporary array

    // push the Title, URL and Date created onto a temporary array
    newImportRow.push(importdata[i+1][0],importdata[i+1][2],importdata[i+1][3])

    // push the results of this row onto the history data array
    historydata.push(newImportRow)
  
  }

  // update the feed data to the Hisry RSS sheet
  historysheet.getRange(historyLR + 1, 1, historydata.length,3).setValues(historydata);

  SpreadsheetApp.flush();

  return;
}

/**
 * Removes duplicate rows from the current sheet.
 * Sourced from Google Apps tutorial
 * https://developers.google.com/apps-script/articles/removing_duplicates
 */
function removeDuplicates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // build the sheet names
  var historyname = "History RSS1";
  var historysheet = ss.getSheetByName(historyname);

  // get the data
  var data = historysheet.getDataRange().getValues();

  // create an array to hold unique rows
  var newData = [];

  // loop through the data testing for duplicate values
  for (var i in data) {
    var row = data[i];
    var duplicate = false;
    for (var j in newData) {
      if (row.join() == newData[j].join()) {
        duplicate = true;
      }
    }
    if (!duplicate) {
      newData.push(row);
    }
  }

  // clear the entire History RSS sheet
  historysheet.clearContents();

  // paste the date from the array
  historysheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

  SpreadsheetApp.flush();
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.