Журнал роботи в Google Sheets та розширення його функцій з JavaScript

Коли я два тижні тому на роботі признався начальнику що вже все зробив, мій начальник вирішив поділитись мною з іншим начальником. Тепер я не можу все зробити, тому що один начальник проводить по дві наради на день на якій мене питають що я зробив, а інший начальник проводить одну нараду на день, на якій теж питають що я зробив. Крім того треба щодня писати звіти, по кожному проекту окремо, бо це впливає на бюджет проектів і на мою зарплатню. І щоб мені було легше пояснити одному начальнику що я сидів на нараді в іншого, та навпаки, я вирішив записувати що роблю протягом дня. Ну звісно виявилось що не багато роблю, але ви коли-небудь пробували записувати що робите? Якщо більше ніж 5 годин корисної роботи на добу – ви мій герой, навчіть мене.

Власне спочатку я хотів завести табличку по методології Pomodoro. Але, виявилось що Pomodoro – це занадто складно коли тебе три наради на день, якщо не рахувати переписок зі співробітниками в Skype. Тому я вирішив просто записувати час початку виконання і час закінчення.

В Google Sheets є дві корисні комбінації клавіш:

1. Ctrl + ; – вставляє в клітинку дату
2. Ctrl + Shift + ; – вставляє в клітинку час. В мене чомусь в форматі AM/PM.

Цього було б досить, але рахувати тривалість задач доводиться вручну. Незручно. Якщо є табличка:

	A	B	C	D
1	Time started	Task	Time finished	Duration
2	1:32 PM	Project A daily stand-up	1:51 PM	0:19

Хочеться щоб колонка D заповнювалась формулою. Але якщо написати в D2 =C2 - A2, воно видасть: “#VALUE!” – Помилка: не число: 1:51 PM. Тому треба використати функції.

Десь я чув фразочку що електронні таблиці – це функціональна мова програмування. Ах. 🙂 Функцій там купа. Клітинок аби зберігати результати їх роботи – теж досить. Але вчити всі ті функції – ну їх.

На щастя Google Docs можна скриптувати за допомогою JavaScript! Ну й правда, не Visual Basic-ом ж користуватись у браузерному застосунку.

Як ми це робимо? Натискаємо “Інструменти” -> “Редактор сценаріїв”. Відкриється онлайновий редактор коду. Вставляємо туди щось таке:

// (c) Salman A     http://stackoverflow.com/a/1788084/816449
function date_diff(date1, date2) {
  if (date2 < date1) {
    date2.setDate(date2.getDate() + 1);
  }
  return date2 - date1;
}

function fmtmsec(msec) {
  var hh = Math.floor(msec / 1000 / 60 / 60);
  msec -= hh * 1000 * 60 * 60;
  var mm = Math.floor(msec / 1000 / 60);
  msec -= mm * 1000 * 60;
  return hh + ':' + mm;
}

function parsetime(cell) {
    var part = cell.match(/(\d+):(\d+)(?: )?(am|pm)?/i);
    var hh = parseInt(part[1], 10);
    var mm = parseInt(part[2], 10);
    var ap = part[3] ? part[3].toUpperCase() : null;
    if (ap === "AM") {
        if (hh == 12) {
            hh = 0;
        }
    }
    if (ap === "PM") {
        if (hh != 12) {
            hh += 12;
        }
    }
    return new Date(2000, 0, 1, hh, mm, 0);
}

function time_diff(arg1, arg2) {
  try {
    arg1 = parsetime(arg1);
    arg2 = parsetime(arg2);
  } catch (e) {
    return '';
  };
  return fmtmsec(date_diff(arg1, arg2));
};

function test() {
  Logger.log(time_diff('9:12 PM', '10:30 PM'));
}

Даємо модулю назву, на зразок Time diff, зберігаємо. В нашій табличці відкриваємо “Інструменти” -> “Менеджер сценаріїв” та переконуємось що наш сценарій там видно.

Тепер, в клітинці D2 можна писати =time_diff(A2;C2). І не поставте кому між аргументами. Буде синтаксична помилка яку я довго шукав в JavaScript, в той час як виявилось що проблема була в записі формули. І це ніби все.

Так просто що мені аж сподобалось.