Skip to content

Instantly share code, notes, and snippets.

@navhits
Last active May 19, 2020 08:22
Show Gist options
  • Save navhits/e8d4392970457ecd062212bfd6fcf599 to your computer and use it in GitHub Desktop.
Save navhits/e8d4392970457ecd062212bfd6fcf599 to your computer and use it in GitHub Desktop.
Calculate the minutes between two datetime in Google Sheets with Google AppScript
var dueBy = "A"; // Provide the appropriate column ID
var todayIs = "B" // Provide the appropriate column ID
var resultField = "C"; // Provide the appropriate column ID
var onTrack = "D" // Provide the appropriate column ID
// first sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
// row range
var startRow = 2; // Mention the range start only
var endRow = sheet.getLastRow();
function getMinutes() {
for (var i=startRow; i<(endRow+1); i++) {
var today = new Date()
var date = (today.getMonth()+1)+'/'+today.getDate()+'/'+today.getFullYear();
var time = today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
var todayDate = date+' '+time;
// Writing the DateTime to sheets for script efficiency
sheet.getRange(todayIs + i).setValue(todayDate);
var todayDateTime = new Date(sheet.getRange(todayIs + i).getValue());
var dueDate = new Date(sheet.getRange(dueBy + i).getValue());
// This calculates the milliseconds
// We divide it by 1000 and by 60 to get minutes
var minutesBeforeDue = parseInt((dueDate - todayDateTime) / 1000 / 60);
// Setting values to the appropriate row
sheet.getRange(resultField + i).setValue(minutesBeforeDue);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment