-1

In sheet 1, I import data from another spreadsheet - "source" using IMPORTRANGE. The source sheet retrieves data from Google Analytics. The source sheet is updated with new data. This is also reflected in sheet 1. The new data could have duplicates from the previous data. What I want is to retrieve the unique data comparing it to previous data or in other words, next time importrange updates I need unique links(data) that were not there at previous impotrrange update.

One way of doing it would be to create a backlog of data between the daily stamps and then do a unique on it. but this bombards the spreadsheet with loads of data and since this is a continuous process, the backlog spreadsheet will append to limit in a few weeks.

Is there a script that can catch the importrange update time and perform a unique at it comparing the previous data?

Basit Moharkan
  • 1
  • Is this what you mean? =UNIQUE(IMPORTRANGE(spreadsheet_url; bereik_tekenreeks)) – timmer Feb 19 '20 at 13:41
  • The `UNIQUE` function will give you unique rows in a range. could you provide a sample of your source and destination sheet? – Aerials Feb 19 '20 at 14:05
  • @timmer this will give me the unique of data to be imported at that particular time. however, the data will already unique at a given instance since the source data is coming from google analytics. What I want is that when new links(data) are retrieved from google analytics into source sheet which is when importrange will retrieve links from source sheet, the new links that apear should be compared with existing data (previous links) and collected in let's say another sheet called sheet 2. When importrange updates data, in sheet 2 the script should compare prevous data with new data – Basit Moharkan Feb 19 '20 at 14:43
  • Also to add, I have to compare the updates from importrange because the past data before the time range will keep on getting deleted in the source sheet – Basit Moharkan Feb 19 '20 at 14:50
  • Use GAS. Use a global variable to store the unique links, and using `indexOf` to verify new links aren't duplicated. Use a trigger to run the GAS and put filtered values in destination. – Aerials Feb 26 '20 at 12:09

2 Answers2

0

You can achieve this by using Google Apps Script to handle the logic and keep unique links in a global variable or script property.

An example:

  1. Source sheet receives new data. --> Trigger your script.
  2. Script consumes links from source; checks against global variable / script property, and appends new unique values to it.
  3. Script injects unique data into destination.

Useful links:

Community
  • 1
  • 1
Aerials
  • 4,231
  • 1
  • 16
  • 20
-1

data > source spreadsheet > (via importrange) destination spreadsheet sheet_1 & new data into destination spreadsheet sheet_2?

If that is what you mean:

function doSomeThing(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheetOne = ss.getSheetByName("AAA");
var dataOne = sheetOne.getRange(1,1,sheetOne.getMaxRows(),sheetOne.getMaxColumns()).getValues();

var sheetTwo = ss.getSheetByName("BBB");


var colNumberDataToCompare = 5; //for example
var dataToCompare = sheetTwo.getRange(1,colNumberDataToCompare,sheetTwo.getLastRow(),1).map(function(cell){return cell[0]}); 
var hlpArray = [];

  for(var i=0;i<dataOne.length;i++){

    if(dataToCompare.indexOf(dataOne[i][colNumberDataToCompare]) == -1){

      hlpArray.push([dataOne[i]])

    }

  }
  if(hlpArray[0]){

    sheetTwo.getRange(sheetTwo.getLastRow()+1,1,hlpArray.length,hlpArray[0].length);

  }

}

If not, sorry.

timmer
  • 159
  • 6