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