Due Dates of Expiry Progress Bar

Question

I'm trying to create a progress bar for due dates of expiry in column D , but may coding not works , it show a numerical with decimal places .

someone helps me to show the progress bar with percentage or status like , "GOOD" , "SLIGHTLY GOOD" , "NOT GOOD" .

function updateProgress() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();

  var dueDates = sheet.getRange(2, 1, lastRow - 1, 1).getValues(); // Assuming due dates start from row 2 in column A
  var progressBars = [];

  var today = new Date();

  for (var i = 0; i < dueDates.length; i++) {
    var dueDate = new Date(dueDates[i][0]);
    var timeDiff = dueDate.getTime() - today.getTime();
    var daysDiff = Math.ceil(timeDiff / (1000 * 3600 * 24)); // Calculating the difference in days

    // Progress calculation based on due date proximity
    var progress = (daysDiff <= 0) ? 100 : Math.max(0, (100 - (daysDiff / 30) * 100)); // Assuming 30 days as the full duration

    progressBars.push([progress]);
  }

  // Update the progress bars in the sheet
  sheet.getRange(2, 2, progressBars.length, 1).setValues(progressBars);
}

Answer 1

Creating Progress Bar based on the Current Percentage from Full Duration:

I have created a simple Apps Script that will satisfy your requirement. Take note that this will be the basic steps on taking it. You can improve the script as you wish if you have more details you want to add. I created 2 Functions to dissect your requirements. First Part is getting from the Sheet and using the formula Math.round((calculateDateDifferenceInDays(today, values[i][1]) / 30) \* 100) to compute for the percentage of the days based off the full duration. The second function is to compute for days. As you can see with the if statements I also assume that you might encounter Non Number Inputs, Blanks I also took the liberty of assuming they have to be skipped.

Code:

function processSheets() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); //Change depending on your Sheet Name.
  var range = sheet.getRange(2, 2, sheet.getLastRow(), 2);
  var values = range.getValues();
  var progressBar = [];
  var today = new Date();
   for (i = 0; i < values.length - 1; i++) {
    if (Math.round((calculateDateDifferenceInDays(today, values[i][1]) / 30) * 100) >= 80) { 
      progressBar.push(["Good"])
    }
    else if (Math.round((calculateDateDifferenceInDays(today, values[i][1]) / 30) * 100) >= 40) {
      progressBar.push(["Slightly Good"])
    }
    else if (Math.round((calculateDateDifferenceInDays(today, values[i][1]) / 30) * 100) >= 1) {
      progressBar.push(["Not Good"]);
    }
    else{
      progressBar.push([" "])
    }
 }
sheet.getRange(2,4, progressBar.length, 1).setValues(progressBar)
}
function calculateDateDifferenceInDays(dateString1, dateString2) {
  const date1 = new Date(dateString1);
  const date2 = new Date(dateString2);
  const differenceInMs = Math.abs(date2 - date1);
  const differenceInDays = Math.floor(differenceInMs / (1000 * 60 * 60 * 24));

  return differenceInDays;
}

Sample Output:

References:

Math Round

Date