Last active
May 19, 2020 08:22
-
-
Save navhits/e8d4392970457ecd062212bfd6fcf599 to your computer and use it in GitHub Desktop.
Calculate the minutes between two datetime in Google Sheets with Google AppScript
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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