Apps Script - Iterating over range based on IDs in a column-CodePudding

I have a sheet with three columns (ID, Stage, Date). Every day, about 10k rows are imported to that sheet. The ID is a Salesforce ID and references an Opportunity. "Stage" is the stage that the opportunity is in on the day of the import. "Date" is always the date of the day of the import.

ID | Stage | Date
0061p00000hx0mS Discovery   2022-07-06
0061p00000hx0mS Discovery   2022-07-07
0061p00000hx0mS Discovery   2022-07-08
0061p00000hx0mS Discovery   2022-07-09
0061p00000hx0mS Discovery   2022-07-10
0061p00000hx0mS Closed Lost 2022-07-11
0061p00000hx0mS Closed Lost 2022-07-12
0061p00000hx0mS Closed Lost 2022-07-13
0061p00000hx0mS Closed Lost 2022-07-14
0061p00000hx0mS Closed Lost 2022-07-15
0061p00000hx0mS Closed Lost 2022-07-16
0061p00000hx0mS Closed Lost 2022-07-17
0061p00000hx0mS Closed Lost 2022-07-18

The stage is related to the ID and may or may not change each day. Since this results in a sheet that becomes large quite fast, I'd like to add a script that runs once per day and removes all rows for the most recent date if the stage for a given ID has not changed compared to the previous date available.

From the example above, the output should look like this:

ID | Stage | Date
0061p00000hx0mS Discovery   2022-07-06
0061p00000hx0mS Closed Lost 2022-07-11

The process of copying the data is handled by an existing script that contains the range of the newly added data. For the sake of this question, assume that rangeValues is the range with the latest added data.

How should I go about this?

CodePudding user response：

It could be something like this:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const [header, ...data] = sheet.getDataRange().getValues();
  const obj = {};

  data.reverse().forEach(([id,stage,date]) => obj[id stage] = [id,stage,date]);

  const table = [header, ...Object.values(obj).reverse()];

  sheet.clearContents()
    .getRange(1,1,table.length,table[0].length)
    .setValues(table);
}

It should work fine if your data is sorted by the dates. Let me know if it doesn't.

And the algo has some limitations. It needs to test on the real data.