CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Nessus B
Nessus B

Reputation: 43

Delete columns before today's date

I've been struggling with this for a couple weeks now. I have posted a question here a couple days a go but still i didn't got and solution yet. Probably because in fact it was a 2-3 tasks question and not so well explained by my fault. Thus i decided to post another one with a one (of the 2-3) more simple task.

So, i have the script below which deletes in the sample sheet the columns dates before today's date. The script works fine but i have two issues.

First... If the number of the old dates/columns is one it works fine. It deletes only the day before and NOT the first 3 columns (A,B & C in the sample sheet) which i want to keep. If they are more the one old dates then the script deletes several columns/dates before and after today's date. What i want is to delete all the columns/dates ONLY before today's date and NOT the first 3 columns (A,B & C in the sample sheet) which i want to keep.

The second issue is kinda weird. Currently i am running the script from a custom menu ("My Tools" in the sample sheet). Now, when run the script from the custom menu or with the "Run" button in script editor it works. When i try to add a time-driven trigger (not programmatically. i don't know how) so to run it once every 24 hours, then i get a script error with this message: Exception: Range not found at deleteColumnsDate(Code:14:49).

This is a sample of my working sheet: https://docs.google.com/spreadsheets/d/1NJvcLxwc96411-Sl_aTu1d-J5gQvqlZjPUQbWZoRqBM/edit?usp=sharing

And this is the script that i am currently using:

function onOpen()
    {
      var ui = SpreadsheetApp.getUi();
      ui.createMenu('My Tools')
            .addItem('Delete Older Dates','deleteColumnsDate')
            .addToUi();
    }

  function deleteColumnsDate(row)
    {
      var row = (typeof(row) !== 'undefined') ? row : '3';
      var day = 86400000;
      var today = new Date().getTime();
      var rng = SpreadsheetApp.getActiveSheet().getRange(row + ':' + row);
      var rngA = rng.getValues();
      for(var i = 1; i < rngA[0].length ;i++)
    {
      if(isDate(rngA[0][i]) && (((today - new Date(rngA[0][i]).getTime())/day) > 1 ))
        {
          SpreadsheetApp.getActiveSheet().deleteColumns(i + 1);
        }

      }
    }

    function isDate (x)  
    { 
      return (null != x) && !isNaN(x) && ("undefined" !== typeof x.getDate); 
    }

So, what am i doing wrong here?. Is it possible to make this process to run fully automatically every 24 hours?

Thank you in advance

Nessus

Upvotes: 0

Views: 271

Answers (1)

Waxim Corp
Waxim Corp

Reputation: 917

When you put a time trigger, you can't use getActiveSheet(). Instead you should use SpreadsheetApp.openById( [ID] )

You will find your ID inside URL of the document like : docs.google.com/spreadsheets/d/abc1234567/edit#gid=0

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openbyidid

EDIT:

As Cooper mentionned it, I forgot something. Try this way :

var ss = SpreadsheetApp.openById( [SHEET ID] );
var sh = ss.getSheetByName( [SHEET NAME] ); //delete column here
var rng = sh.getRange(row + ':' + row);

Complete function:

  function deleteColumnsDate() {

    var day = 86400000;
    var today = new Date().getTime();

    var ss = SpreadsheetApp.openById(" [SHEET ID]");
    var sh = ss.getSheetByName(" [SHEET NAME] ");
    var rng = sh.getRange('A1:Z1'); // change if you need larger
    var rngA = rng.getValues();

    var nb_to_delete = 0;
    for(var i = 1; i < rngA[0].length ;i++) { // start colonne B
      if(isDate(rngA[0][i]) && (((today - new Date(rngA[0][i]).getTime())/day) > 1 )){
        nb_to_delete++;
      }
    }
    sh.deleteColumns(2, nb_to_delete);
  }

Upvotes: 3

Related Questions