CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamp
Alberto
Alberto

Reputation: 19

Script to delete rows based on a time value in a cell in Google Sheets

I am trying to write a Script to delete rows based on the time value contained in a cell in Google Sheets, the value in the cell only has AM or PM time, there is no date. I have been experimenting but so far I have not been able to do it. Could someone please be so kind to give me an indication as to what conditions should be defined in the Script to meet this condition? The purpose behind this behavior is to show only posts that have been published within an hour. For this I will be automatically running the Script every hour. :)

Data Table:

HOURS MINUTES LATITUDE LONGITUDE ADDRESS POSTED AT BRAND
1 0 28.68062 14/9/1899 20:49:12 Del Paraso 8716, Nuevo Paraso, 31123 Chihuahua, Chih., Mexico 11:53:24 AM ESSO"]
1 0 28.68057 14/9/1899 20:49:08 Del Paraso 8716, Nuevo Paraso, 31123 Chihuahua, Chih., Mexico 1:01:47 PM ESSO"]
1 0 28.68069 14/9/1899 20:49:10 Del Paraso 8716, Nuevo Paraso, 31123 Chihuahua, Chih., Mexico 2:17:48 PM ESSO"]
0 2 28.68061 14/9/1899 20:49:13 Del Paraso 8716, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 5:36:55 PM ESSO"]
0 1 28.68068 14/9/1899 20:49:06 Del Paraso 8720, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 5:57:09 PM SHELL"]
1 1 28.68059 -106.13241 Del Paraso 8716, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 9:23:42 PM ESSO"]
2 3 28.68074 -106.13249 Medina 8717, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 1:35:45 PM SHELL"]
2 1 28.68082 -106.13253 Del Paraso 8720, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 1:43:11 PM ESSO"]
3 0 28.6805 -106.13283 Siria 2808, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 1:44:49 PM ESSO"]
8 59 28.64719014 -106.0999422 Blvrd Antonio Ortiz Mena 411, Unidad Presidentes, 31210 Chihuahua, Chih. 1:44:49 PM PEMEX
1 0 28.68067 -106.13255 Del Paraso 8720, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 5:59:42 PM ESSO"]
1 1 28.68069 -106.13256 Del Paraso 8720, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 6:07:49 PM BP"]
2 1 28.68071 -106.13255 Del Paraso 8720, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 4:42:28 PM ESSO"]

Upvotes: -1

Views: 500

Answers (1)

Cooper
Cooper

Reputation: 64040

This function will delete every thing older than about 2 hours.

function deleteRowBasedUponTime() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const sr = 2;
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn()).getDisplayValues();
  Logger.log(JSON.stringify(vs));
  const dtv = Utilities.formatDate(new Date(new Date().getFullYear(), new Date().getMonth(), new Date().getDate(), new Date().getHours() - 2), ss.getSpreadsheetTimeZone(), "hh:mm:ss a").split(/:| /).reduce((a, v, i, arr) => {
    if (i == 0) a.sum += parseInt(v) * 3600;
    if (i == 1) a.sum += parseInt(v) * 60;
    if (i == 2) a.sum += parseInt(v);
    if (i == 3 && v == "PM" && parseInt(arr[0]) != 12) a.sum += 43200;
    return a;
  }, { sum: 0, total: function () { return this.sum } }).total();
  Logger.log(dtv)
  let k = [];
  vs.forEach((r, i) => {
    let item = r[0];
    let itmv = r[0].split(/:| /).reduce((a, v, i, arr) => {
      if (i == 0) a.sum += parseInt(v) * 3600;
      if (i == 1) a.sum += parseInt(v) * 60;
      if (i == 2) a.sum += parseInt(v);
      if (i == 3 && v == "PM" && parseInt(arr[0]) != 12) a.sum += 43200;
      return a;
    }, { sum: 0, total: function () { return this.sum } }).total();
    if (itmv > dtv) {
      k.push(r)
    }
  });
  if (k && k.length > 0) {
    sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn()).clearContent();
    sh.getRange(sr, 1, k.length, k[0].length).setValues(k);
  }
}

Timebased Trigger

function onehourtrigger() {
  //this conditional ensures that you do not create more than one trigger for the same function
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "deleteRowBasedUponTime").length == 0) {
    ScriptApp.newTrigger("deleteRowBasedUponTime").timeBased().everyHours(1).create();
  }
}

You can goto to Google Apps Script Reference and using the search box find any function that you don't understand.

If it's a pure JavaScript function the go here

Try it this way:

function deleteRowBasedUponTime() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Diesel");
  const sr = 2;
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn()).getDisplayValues();
  Logger.log(JSON.stringify(vs));
  const dtv = Utilities.formatDate(new Date(new Date().getFullYear(), new Date().getMonth(), new Date().getDate(), new Date().getHours() - 2), ss.getSpreadsheetTimeZone(), "hh:mm:ss a").split(/:| /).reduce((a, v, i, arr) => {
    if (i == 0) a.sum += parseInt(v) * 3600;
    if (i == 1) a.sum += parseInt(v) * 60;
    if (i == 2) a.sum += parseInt(v);
    if (i == 3 && v == "PM" && parseInt(arr[0]) != 12) a.sum += 43200;
    return a;
  }, { sum: 0, total: function () { return this.sum } }).total();
  Logger.log(dtv)
  let k = [];
  vs.forEach((r, i) => {
    let item = r[5];
    let itmv = r[5].split(/:| /).reduce((a, v, i, arr) => {
      if (i == 0) a.sum += parseInt(v) * 3600;
      if (i == 1) a.sum += parseInt(v) * 60;
      if (i == 2) a.sum += parseInt(v);
      if (i == 3 && v == "PM" && parseInt(arr[0]) != 12) a.sum += 43200;
      return a;
    }, { sum: 0, total: function () { return this.sum } }).total();
    if (itmv > dtv) {
      k.push(r)
    }
  });
  if (k && k.length > 0) {
    sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn()).clearContent();
    sh.getRange(sr, 1, k.length, k[0].length).setValues(k);
  }
}

I also set the format to time and went through each item of the list changing them to a more standardized format.

This is the resulting page after running the code:

HOURS MINUTES LATITUDE LONGITUDE ADDRESS POSTED AT BRAND
0 2 28.68061 14/9/1899 20:49:13 Del Paraso 8716, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 5:36:55 PM ESSO"]
0 1 28.68068 14/9/1899 20:49:06 Del Paraso 8720, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 5:57:09 PM SHELL"]
1 1 28.68059 -106.13241 Del Paraso 8716, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 9:23:42 PM ESSO"]
1 0 28.68067 -106.13255 Del Paraso 8720, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 5:59:42 PM ESSO"]
1 1 28.68069 -106.13256 Del Paraso 8720, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 6:07:49 PM BP"]
2 1 28.68071 -106.13255 Del Paraso 8720, Nuevo Paraso, 31123 Chihuahua, Chih., Mxico 4:42:28 PM ESSO"]

Upvotes: 1

Related Questions