Hi I'm using this script to rename every sheet by inserting 'Copy of' in front of the existing sheet name where the text in cell 'B36' = 'SAFETY ANALISIS' and the date from cell 'K3'is older then 30 days. My issue is having to do with the date I can't quite figure how to do it. Cell 'K3' cell are in this format "1-Aug-2021" I think I need to convert the date in 'K3' to a number format. Any help would be greatly appreciated

function getSheet() {
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var sum = 0;
    for (var i = 0; i < sheets.length ; i   ) {
        var sheet = sheets[i];
        var date = new Date();
        var ageInDays = 30;
        var threshold = new Date(
                      date.getFullYear(),
                      date.getMonth(),
                      date.getDate() - ageInDays)
                    .getTime();

        var val = sheet.getRange('K3').getValue();
        var val2 = sheet.getRange('B36').getValue();
       
       


        if (val >= threshold && val2 == 'SAFETY ANALYSIS') {
            var sheetName = sheet.getName()
            sheet.setName('Copy Of ' sheetName)  
        }       
    }

  
}

CodePudding user response：

You may want to wrap the value you get from cell K3 in a Date() constructor. That should work with spreadsheet dates as well as text strings that look like dates.

I think you have the comparison in val >= threshold the wrong way around. Try something like this:

function renameOldSafetyAnalysisSheets() {
  const timeLimit = 30 * 24 * 60 * 60 * 1000; // 30 days
  const now = new Date();
  const sheets = SpreadsheetApp.getActive().getSheets();
  sheets.forEach(sheet => {
    if (sheet.getRange('K3').getValue() !== 'SAFETY ANALYSIS') {
      return;
    }
    const date = new Date(sheet.getRange('B36').getValue());
    if (!date.getTime()
      || now.getTime() - date.getTime() < timeLimit) {
      return;
    }
    try {
      sheet.setName('Copy of '   sheet.getName());
    } catch (error) {
      ;
    }
  });
}

CodePudding user response：

function getSheet() {
  const shts = SpreadsheetApp.getActive().getSheets();
  let d = new Date();
  let ageInDays = 30;
  let threshold = new Date(d.getFullYear(),d.getMonth(),d.getDate() - ageInDays).valueOf();
  shts.forEach(sh => {
    let val = new Date(sh.getRange('K3').getValue()).valueOf();
    let val2 = sh.getRange('B36').getValue();
    if (val <= threshold && val2 == 'SAFETY ANALYSIS') {
      sh.setName('Copy Of '   sh.getName())
    }
  });
}