-1

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"]
Improve this question
2
  • Can you provide a sample sheet/demo sheet with a sample data along with your desired output as an example? Commented Dec 2, 2021 at 19:42
  • Here are a couple of images of an experimental Google Spreadsheet. The first one shows the Spreadsheet before running the Script and the second shows the same Spreadsheet after the Script has been run. Img 1: drive.google.com/file/d/1Ywf-Hr7c1neeMqXat1klhBCKwwuuI930/… Img: drive.google.com/file/d/130D68BFD3Nhi5rT7PkEp2sV9VlKP_qTh/… The idea behind the Script is to run it every hour to detect which posts are over an hour old thus deleting them and leaving the ones that are less than one hour old. Hope this explains what I need. Commented Dec 2, 2021 at 23:30

1 Answer 1

Reset to default
1

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"]
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you very much for your response I've tried your code and it works though it is deleting all the posts in the Spreadsheet, even the ones that are less that two hours old, is there something I should change or that I am overlooking? Also when running the script I get this error message: Error TypeError: Cannot read property 'length' of undefined
I just copied and pasted into my editor and ran with the data that I had before and it works.
I solved the error problem. It occurred because I guess nothing in your list passed the conditional test. Please share a table of your data
Hi, here is the link to my table: docs.google.com/spreadsheets/d/…
Had to change the column index from 0 to 5
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.