Convert number to time (mm:ss)

Question

I would like to use a script to convert the column B2:B (text format) into minutes and seconds (mm:ss). I can do this with a formula (=B2/86400), but it should be implemented with a script:

COLUMN B    (COLUMN B)

244         (= 04:04)
211         (= 03:31)
229         (= 03:49)
246         (= 04:06)

how do I do that?

Answer 1

You can achieve this conversion using Google Apps Script in Google Sheets. Here's a script that converts the numbers in column B to minutes and seconds format (mm:ss) and populates the adjacent cells in column C:

function convertToTime() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange('B2:B' + sheet.getLastRow());
  const values = range.getValues().map(([seconds]) => {
    const minutes = Math.floor(seconds / 60);
    const paddedSeconds = seconds % 60 < 10 ? '0' : '';
    return `${minutes}:${paddedSeconds}${seconds % 60}`;
  });
  range.setValues(values);
}

Make sure your column B contains only numbers representing seconds. If there are any non-numeric values, the script will throw an error.