Convert duration from text to a time value (eg. 2 h 15 min to 2h 15m 0s) in Google Sheets

user_17398976 0
Viewed 91

I have a set of data that is being copy-pasted onto a sheet. The data has a column for duration that is written as X h X min. However, the values are not recognized as number values so the data can't be used for any calculations. I found an onEdit script that will change the text to a proper time value, but it will only work if I edit each cell one by one.

Is there any way to replace this script with one that can be triggered with a button instead of each time I edit the cell?

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"');
    }
  }
}

I've tried the following, but it does not work:

function setDuration(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var value = sheet.getRange("C45:C80").getValues();
  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.setValues(duration).setNumberFormat('[h]"h "m"m "s"s"');
    }
  }
}

I'm having trouble wrapping my head around how onEdit scripts work. I understand I need to set up a for loop or an array, but I'm confused on how those work as well.

google-apps-script google-sheets
1 Answers

In your situation, how about the following modification?

Modified script:

function setDuration(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("C45:C80");
  var values = range.getValues();
  var formats = range.getNumberFormats();
  var {converted, format} = values.reduce((o, [value], i) => {
    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;
        o.converted.push([duration])
        o.format.push(['[h]"h "m"m "s"s"']);
      } else {
        o.converted.push([value]);
        o.format.push(formats[i]);
      }
    } else {
      o.converted.push([value]);
      o.format.push(formats[i]);
    }
    return o;
  }, {converted: [], format: []});
  range.setValues(converted).setNumberFormats(format);
}

  • In this modification, your script for converting the string to the date, and the number format is copied to the cells.

  • In your script, value is a 2-dimensional array. By this, your if statement is always false. By this, your script in the if statement is not run. I thought that this might be the reason for your issue.

Reference:

user_71086530
Related