Delete outdated information using Google Apps Scripts when the date is less than the current date plus a specified number of days

Question

Delete outdated information using Google Apps Scripts when the date is less than the current date plus a specified number of days

I have a Google Sheet where I need to filter out entries based on the number of days since the last check. Specifically, I want to keep only those entries where the number of days since the last check is greater than 10.

You can find the Sheet here.

function check(){

/** Setting Variables **/ 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sh1 = ss.getSheetByName('Data Processing');

/** Removing Duplicates **/ 
  var sh1data = sh1.getDataRange().getValues(); 
  var sh1newData = []; 
  for (var i in sh1data) { 
    var row = sh1data[i]; 
    var duplicate = false; 
    for (var j in sh1newData) {
/* Checking Date */
      var today=new Date().valueOf(); 
      var sec=1000;  
      var min=60*sec;  
      var hour=60*min;  
      var day=24*hour; // Convert time units
      var sh1DateChecked = sh1newData[j][4].valueOf(); 
      var diff=today-sh1DateChecked; 
      var days=Math.floor(diff/day); // Calculate Days since last check
      
if(row[0] == sh1newData[j][0] && row[1] == sh1newData[j][1] && days > 10)
{ duplicate = true; } } 
    if (!duplicate) { sh1newData.push(row);
                     
} 
  } 
  sh1.clearContents();
  sh1.getRange(1, 1, sh1newData.length, sh1newData[0].length).setValues(sh1newData); 

}

javascript google-apps-script

Answer 1

Answer №1

In solving the problem at hand, the focus is on removing dates less than 10 days old rather than dealing with duplicates as mentioned in the code. However, adding functionality to remove duplicates can easily be incorporated if needed.

A suggestion is to encapsulate the condition within a function that takes a row of data as input and outputs a boolean value, like so:

function dateCheckedOlderThan10Days(row) {
  return getDays(row[4]) > 10;
}

function getDays(date) {
  const sec = 1000;
  const min = 60 * sec;
  const hour = 60 * min;
  const day = 24 * hour;  
  return Math.floor((new Date() - date)/day);  
}

(Notice how the getDays function can be extracted out from the main function for better code organization.)

This function seamlessly integrates into Array.prototype.filter, providing clarity on the expected behavior through its naming.

const dataOlderThan10Days = ss.getSheetByName('Data Processing')
  .getDataRange()
  .getValues()
  .filter(dateCheckedOlderThan10Days);

The refactored checked function broken down into smaller functions:

Code.js

function check() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName('Data Processing');
  const data = dataSheet.getDataRange().getValues();
  const dataOlderThan10Days = data
    .slice(1) // excludes header row; adjust according to presence of headers
    .filter(dateCheckedOlderThan10Days);
  dataSheet.clearContents();
  //dataOlderThan10Days.unshift(data[0]); // <-- include if restoring headers is needed
  setData(dataSheet, dataOlderThan10Days);
}

function dateCheckedOlderThan10Days(row) {
  return getDays(row[HEADINGS.DATE_CHECKED]) > 10;
}

const HEADINGS = {
  FIRST_NAME: 0,
  LAST_NAME: 1,
  SALARY: 2,
  AGE: 3,
  DATE_CHECKED: 4
};

function setData(sheet, data) {
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

function getDays(date) {
  const sec = 1000;
  const min = 60 * sec;
  const hour = 60 * min;
  const day = 24 * hour;  
  return Math.floor((new Date() - date)/day);  
}

Answer 2

In solving the problem at hand, the focus is on removing dates less than 10 days old rather than dealing with duplicates as mentioned in the code. However, adding functionality to remove duplicates can easily be incorporated if needed.

A suggestion is to encapsulate the condition within a function that takes a row of data as input and outputs a boolean value, like so:

function dateCheckedOlderThan10Days(row) {
  return getDays(row[4]) > 10;
}

function getDays(date) {
  const sec = 1000;
  const min = 60 * sec;
  const hour = 60 * min;
  const day = 24 * hour;  
  return Math.floor((new Date() - date)/day);  
}

(Notice how the getDays function can be extracted out from the main function for better code organization.)

This function seamlessly integrates into Array.prototype.filter, providing clarity on the expected behavior through its naming.

const dataOlderThan10Days = ss.getSheetByName('Data Processing')
  .getDataRange()
  .getValues()
  .filter(dateCheckedOlderThan10Days);

The refactored checked function broken down into smaller functions:

Code.js

function check() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName('Data Processing');
  const data = dataSheet.getDataRange().getValues();
  const dataOlderThan10Days = data
    .slice(1) // excludes header row; adjust according to presence of headers
    .filter(dateCheckedOlderThan10Days);
  dataSheet.clearContents();
  //dataOlderThan10Days.unshift(data[0]); // <-- include if restoring headers is needed
  setData(dataSheet, dataOlderThan10Days);
}

function dateCheckedOlderThan10Days(row) {
  return getDays(row[HEADINGS.DATE_CHECKED]) > 10;
}

const HEADINGS = {
  FIRST_NAME: 0,
  LAST_NAME: 1,
  SALARY: 2,
  AGE: 3,
  DATE_CHECKED: 4
};

function setData(sheet, data) {
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

function getDays(date) {
  const sec = 1000;
  const min = 60 * sec;
  const hour = 60 * min;
  const day = 24 * hour;  
  return Math.floor((new Date() - date)/day);  
}

Delete outdated information using Google Apps Scripts when the date is less than the current date plus a specified number of days

Answer №1

Code.js

Similar questions

Attempting to call a nested div class in JavaScript, but experiencing issues with the updated code when implemented in

Unable to load circles on page refresh with amCharts Maps

What is the best way to ensure that consecutive if blocks are executed in sequence?

Alternative for Jquery: a new Hash Table solution using Prototype JS

Uncovering the hidden gems within a data attribute

What could be causing the data in the data table to remain undeleted unless the page is manually refreshed

MUI: Autocomplete received an invalid value. None of the options correspond to the value of `0`

The Ajax function is unable to accept JSON data as input

Converting an object of objects into an associative array using Javascript and JSON

Visual Studio Code encounters a JavaScript NPM error that is causing unexpected issues

Is there anything else I should attempt in order to fix this npm start error?

When using the e.target.getAttribute() method in React, custom attributes may not be successfully retrieved

Tips for refreshing an html table without affecting the scroll location?

What is the best method for deleting a portion of a string following the final instance of a particular character?

Leveraging JavaScript to create a horizontal divider

Using JavaScript to dynamically alter the background image of an HTML document from a selection of filenames

searchByTextContentUnderListItemAnchorTag

Issues with React Native imports not functioning properly following recent upgrade

What makes using the `@input` decorator more advantageous compared to the usage of `inputs:[]`

Organize the array by property name and include a tally for each group