Convert numbers to time in Google Spreadsheet

Question

I'm trying to build an automated Google Sheets spreadsheet where I can enter the date, start time, lunch time, end time, number of hours worked per day, and finally sum up the number of hours worked for a month. Creating a work schedule in Google Spreadsheet isn't too difficult, but there are some improvements I'd like to make. One is that even though the spreadsheet correctly calculates the number of hours worked for the day, it displays the total as clock time, such as 8:22 PM, rather than 8 hours and 22 minutes. Another thing is that this format forces me to write hours in time format, which is tedious. I want to be able to enter 3 or 4 numbers into a cell and have them converted to a time.

I learned that the only way to achieve this was to use Google Apps Script, so I followed the only tutorial I found, but the code didn't work at all. The script tool uses JavaScript, which I have some familiarity with because I use it every day in After Effects expressions, but I'm no expert. If anyone could help me with this less challenging project I would be very grateful. Of course, if someone has a better alternative to whatever I'm doing, I'm totally open to redirecting it. Thank you so much. I'll paste the code I created based on the tutorial, and then a link to the tutorial itself.

var COLUMN_TO_CHECK = 3;
var SHEET_NAME = "Sheet1";

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  if(sheet.getName() === SHEET_NAME){
    var selectedCell = sheet.getActiveCell();

    selectedCell.setNumberFormat("@");

    if(selectedCell.getColumn() === COLUMN_TO_CHECK){
      var cellValue = selectedCell.getValue();
      var time = cellValue.substr(0,2) ":" cellValue.substr(cellValue.lenght - 2);
      selectedCell.setValue(time);
      selectedCell.setNumberFormat("hh:mm");
     }
    }
  }

I encountered an error on line 7: var sheet = ss.getActiveSheet();. The debugger calls the variable ss null.

One issue I noticed with the tutorial is that it has inconsistencies between the featured code and the step-by-step code, and they also skip some lines of code. This may be why my code doesn't work. When I followed the tutorial it led me to a dead end where I tried to copy the featured code exactly.

https://yagisanatode.com/2018/06/02/google-apps-script-how-to-automatically-generate-a-time-from-a-four-digit-24-hour-time- in -google-sheets/

Answer 1

I am the author of the article you quote here. I don't use StackOverflow often enough to add this in the comments, I believe the preference here is to do so.

Please note that the ss variable refers to the active spreadsheet. This is the spreadsheet tied to script . Because we are using onEdit() trigger, this script needs to be bound to Google Sheets. Based on your question, I'm not sure if you've already done this, but here are the instructions to accomplish it: In your Google Sheets menu, go to >Extensions>Apps Script.

Here, paste the code and save the project. Make sure your sheet name (the name in the tab) is the same as the sheet name you wish to modify and update, and update the SHEET_NAME variable accordingly.

This should resolve the error you are experiencing.

NOTE - The code breakdown below the main code is only intended to highlight some important parts of the code and is not a step-by-step guide. I've updated the post with this in mind and appreciate your feedback.

I apologize if I misunderstood your question.

Tanaike - If you think this is more appropriate, please feel free to delete this reply or merge it into a comment instead of posting it here.