RSS News Feed collection into Google Sheet Problem

Question

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

Answer 1

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