Convert numbers to time in Google Spreadsheet
<p>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. </p>
<p>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. </p>
<pre class="brush:php;toolbar:false;">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");
}
}
}</pre>
<p>I encountered an error on line 7: var sheet = ss.getActiveSheet();. The debugger calls the variable <strong>ss</strong> null. </p>
<p>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. </p>
<p>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/</p>