0

I'm setting up a sheet to help coworkers keep track of their appointments. For now the only way to get those times is to copy-paste the numbers from a different program used to track the info. The information pasted on the sheets shows the appointment lengths as X h X m (eg. 2 h 15 min).

The issue is the sheet will view this as text rather than a time value so I can't use the numbers to calculate anything. I found an onEdit script that will convert the text into the time values, however it requires me to go through and edit each cell (add or remove a space) before it will convert the text.

I've already set up a few scripts on this sheet so I'm hoping I can replace the onEdit script with one that can be run by clicking a button like the other scripts I have. The data will be pasted into same range so setting the range of where to convert will always match up.

Script from other answer:

function onEdit(e) {
  var value = e.value;
  if (typeof value == 'string') {
    var match = value.match(/(\d+) ?h/i);
    var hours = match ? match[1] : 0;
    match = value.match(/(\d+) ?m/i);
    var minutes = match ? match[1] : 0;
    match = value.match(/(\d+) ?s/i);
    var seconds = match ? match[1] : 0;    
    if (hours || minutes || seconds) {
      var duration = hours/24 + minutes/1440 + seconds/86400;
      e.range.setValue(duration).setNumberFormat('[h]"h "m"m "s"s"');
    }
  }
}

edit: Changed my wording a bit to make it more clear that I want to replace the onEdit script.

edit 2: I've tried doing this to the script but it does not work:

function setDuration(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var value = sheet.getRange("C45:C80");
  if (typeof value == 'string') {
    var match = value.match(/(\d+) ?h/i);
    var hours = match ? match[1] : 0;
    match = value.match(/(\d+) ?m/i);
    var minutes = match ? match[1] : 0;
    match = value.match(/(\d+) ?s/i);
    var seconds = match ? match[1] : 0;    
    if (hours || minutes || seconds) {
      var duration = hours/24 + minutes/1440 + seconds/86400;
      range.setValue(duration).setNumberFormat('[h]"h "m"m "s"s"');
    }
  }
}

I'm not too familiar with how onEdit scripts work so I'm not sure how to properly convert the script.

2
  • 1
    Thanks for your reply to my comments in other post. It looks that you need to study more. I suggest you to start by reading developers.google.com/apps-script/guides/sheets and developers.google.com/apps-script/guides/support/best-practices.
    – Rubén
    Nov 12 at 20:31
  • 1
    PS.: 1. sheet.getRange("C45:C80") returns a Class Range object, not a value. 2. You might use sheet.getRange("C45:C80").getValues() to get the range values, but your code also have to iterate someway over them i.e by using a for loop or an Array method like Array.prototype.map. The best practices recommends to avoid calling Google Apps Script classes in loops, so instead of using range.setValue(duration) on each iteration it's better to use range.setValues(durations) after the loop where durations is an Array of Arrays containing the transformed values.
    – Rubén
    Nov 12 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.