CODEWITHSUNDEEP
javascriptdatetimegoogle-apps-scriptgoogle-sheets
Phil
Phil

Reputation: 45

How to calculate time via iteration

how does Google script handle time and dates.

If your sheet has time values entered into cells (2:00, 1:30, etc, representing 2 hours and 1 hour 30) and you want to iterate through the rows, adding the total up (so the above would result in 3:30); how do you tell google script to add them together; when referring to what you know to be a time, do you ALWAYS have to use 'new Date({INSERT CELL HERE})'?

I'm trying to get the code to add up all the time values and IF it's more than 22.5 hours (22 hours 30 minutes - not half ten PM) do something.

function ValidateForm(Action) {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
var totalpaidovertime = 0.0;
var totalinlieutime = 0.0;
  var totalhours =  0.0; //new Date("January 01, 2019 00:00");
var validatedtotalhours = 0.0;
var validatedinlieuhours = 0.0;
var validatedtotalpaidovertime = 0.0;
var ui = SpreadsheetApp.getUi();
var hoursclaimed = sheet.getRange("F4:F35").getValues();

  if(spreadsheet.getActiveSheet().getName()=="Totals"){
    Browser.msgBox("Incorrect Sheet For This Action","Please select a users sheet to validate first and try again.",ui.ButtonSet.OK);
  }else{
    if (Action == "Check Overtime"){
      //get totalhours
      for (var r=0;r<=hoursclaimed.length;r++){
        if (hoursclaimed[r].getValue() !=""){
          totalhours+=hoursclaimed[r];       
        }
      }   
      Logger.log(sheet.getRange("f4").getValue()
      //calculate totalpaidovertime variable - 22.5 is 3 days of 7.5 work day.
      if(totalhours > 22.5) {
        totalpaidovertime = 22.5;
      }
      else {
        totalpaidovertime = totalhours;
      }
      //calculate totalinlieutime variable
      if(totalhours > 22.5) {
        totalinlieutime = totalhours - 22.5;
      }
      else {
        totalinlieutime = 0.0;
      }

      if (totalhours > 22.5) {
        Browser.msgBox("Total number of hours equals " + totalhours + " hours. Only 22.5 hours (3 days) will be added to Paid Overtime.");
      }

Upvotes: 1

Views: 96

Answers (2)

Andres Duarte
Andres Duarte

Reputation: 3340

There’s no specific method to achieve what you want, but you can use the displayValues function [1] to obtain the value as a string and manipulate it to fulfill your ambitious goal.

Here is an example implementation I made and tested successfully, you only have to change these two parts of your code:

1) To obtain the array of arrays, each array with the string required as their only value or empty if the cell is empty: var hoursclaimed = sheet.getRange("F4:F35").getDisplayValues();

2) For this case, when the cell is empty it returns an undefined object, so to avoid the empty cells I used the isArray() function. Then you get the string and manipulate it with the substring function to obtain the minutes and hours, convert them to numbers and work with them.

  for (var r=0;r<=hoursclaimed.length;r++){
    var valueArray = hoursclaimed[r];

    if (Array.isArray(valueArray)){
      var str = valueArray[0];
      var minutes = Number(str.substring(str.length - 2, str.length));
      var hours = Number(str.substring(0, str.length - 3));
      totalhours+=(minutes/60) + hours;       
    }
  }

Other solution:

You could also use getValues() function as you have it now (which will get you the dates array), and then use the javascript functions “getHours” and “getMinutes” rather than manipulate the strings:

var minutes = date.getMinutes();
var hours = date.getHours();

[1] https://developers.google.com/apps-script/reference/spreadsheet/range#getDisplayValues()

Upvotes: 1

Wicket
Wicket

Reputation: 38140

Passing durations from a spreadsheet to server-side code on Google Apps Script is tricky because Google Sheets and Google Apps Script/JavaScript use different Epochs. The good news is that we could use getDisplayValue() and getDisplayValues() that in several scenarios make it easier to get the durations as text valuse then parse each part of the time first as text then convert them to numbers.

Upvotes: 1

Related Questions