Google sheets – организация и напомняне

През септември месец ще ходим на Карадере, на дивото. Ще сме известно количество хора. Организацията за подобно пребиваване в дивото не е най-лесното нещо на света, особено за голяма група от хора. В резултат на това реших да поема нещата в свои ръце. Каква изненада.

Като организационен инструмент реших, че ще е по-лесно да използваме някой онлайн инструмент за колаборация. Първоначално реших да използвам self-hosted инструмент. Харесах няколко, но тук се явиха негавитие. На първо място, хората трябва да си направят регистрация. Дори да се съгласят да я направят, трябва да бъдат активни по някакъв начин или поне да имат лесен метод за проследяване на промените. Това означава, че трябва да има push notification service, който пак да е self-hosted, защото отдавна най-често се използват смартфони, а не компютри. Най-вероятно този инструмент няма да има функционалност всеки ден да ми дава отчет какво е свършено и какво не е. Това означава, че трябва да си допиша такава функционалност, което свежда възможностите до използване на нещо Python базирано или в най-лошия случай нещо PHP базирано. Рекапитулацията казва, че трябва да имам два self-hosted инструмента, като по всяка вероятност дописването на функционалности няма да се ограничи само до желания дневен отчет.

Онлайн инструменти, които изискват регистрация – може, но надали хората ще се занимават да си свалят програма на телефона или да следят някакъв сайт.

Тук идва нещо позитивно. Покрай цялото смачкване, източване на информация от смартфони и хронологично проследяване, Google имат инструменти, които на практика са алтернатива на Microsoft Office. Не харесвам Google отдавна, но и отдавна съм си продал душата на тях. Паралелно с мен, на практика всеки потребител на смартфон има акаунт в Google.

Разгледах инструмента им Google sheets. Не харесвам подобни базирани на excell инструменти, но това ми се стори, че е най-добрия вариант за онлайн задружно организиране на задачи. В момента таблицата изглежда горе-долу така:

Стандартна таблица, възможно най-базова. Заради тревожността си, че може да не се организираме добре, на мен ми трябваше инструмент, който да ми изпраща непомняния или отчет за това какво е свършено и какво не е.

Разгледах възмжоностите това да се случи. Google предлагат вариант за писане на Google Apps Script, който може да ми свърши нужната работа. Речено-сторено. Разгледах документацията му, разгледах и малко повече документи за работа с цялостната концепция на Google Apps. В резултат на това, написах следния скрипт:

function checkStatusAndSendEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); 
  var data = sheet.getDataRange().getValues();
  var email = "[email protected]"; 
  var subject = "[Daily Status Reminder] Карадере, 2024";
  var message = "<p>Таблица с нещата, които още не са свършени. Можеш да тормозиш отговорниците:</p><table border='1' style='border-collapse:collapse;'><tr><th>Елемент</th><th>Количество</th><th>Приоритет</th><th>Отговорник</th><th>Статус</th><th>Забележка/коментар</th></tr>";
  var hasItemsToRemind = false;

  for (var i = 1; i < data.length; i++) {
    var status = data[i][4].toLowerCase();
    if (status !== "готово" && status !== "избери") {
      hasItemsToRemind = true;
      var rowColor = "";
      if (status === "не е готово") {
        rowColor = "style='background-color:#f8d7da'"; // Light red background for "не е готово"
      } else if (status === "в процес") {
        rowColor = "style='background-color:#fff3cd'"; // Light yellow background for "в процес"
      }
      
      message += "<tr " + rowColor + ">";
      for (var j = 0; j < data[i].length; j++) {
        message += "<td>" + data[i][j] + "</td>";
      }
      message += "</tr>";
    }
  }

  message += "</table>";

  if (hasItemsToRemind) {
    MailApp.sendEmail({
      to: email,
      subject: subject,
      htmlBody: message
    });
  }
}

function createDailyTrigger() {
  ScriptApp.newTrigger('checkStatusAndSendEmail')
    .timeBased()
    .atHour(9) 
    .everyDays(1)
    .create();
}

Google Apps Script е базиран на JavaScript, което до някъде ме улесни. Скриптът има две функции – checkStatusAndSendEmail и createDailyTrigger.

Първата функция взима съдържанието от листа „Sheet1“, както и редовете, в които колона E съдържа редове, които са различни от „готово“ и „избери“. Взимайки съдържанието, съставя имейл с таблица, в която ми изпраща резултата. За мое улеснение, сложил съм цветове за редовете, чийто статус е „не е готово“ и „в процес“.

Втората функция вероятно не е много нужна, но служи уж за ориентация кога да бъде изпратено писмото, макар реално това да се контролира от тригери, които грубо казано са нещо като cron jobs в GNU/Linux.

Резултатът от този скрипт е, че всяка сутрин получавам следното красиво писмо:

Цялото това нещо ме навежда на разни мисли. Една от тях е, че Кали покрай всичките неща, с които се занимава, прави и застраховки. Ако седнем да помислим някаква форматирана таблица, която чрез помощта на скрипт да ѝ изпраща имейли и напомняния, това ще ѝ улесни работата чувствително.

По-притеснителните ми мисли са свързани с това кога точно Google ще се сетят, че могат да печелят пари и от такива дреболии и ще направят услугата платена. Лично на мене ми бяха нужни над 10 години, за да си нагодя по личните нужди свой собствен вариант на Google Reader. Впрочем, това е единствената им услуга, която ми липсва.

Та…ако имам малко повече късмет, ще успея да автоматизирам доколкото е възможно работата на Кали.

Чудя се обаче дали не е хубаво такива проекти да ги слагам в git, за да мога да имам елементарен контрол над версиите.

Добавка 1, 31.08.2024:

В процеса на работа се оказа, че изникват предложения, за които няма отговорник. Това налага да се добави още една таблица в имейла, който се праща всяка сутрин. В тази таблица трябва да се съдържат всички редове, за които има съдържание в клетка от колона A (елемент), но няма съдържание в колона D (отговорник). Това налага преработка на скрипта по следния начин:

function checkStatusAndSendEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); // Change 'Sheet1' to your sheet name
  var data = sheet.getDataRange().getValues();
  var email = "[email protected]"; 
  var subject = "[Daily Status Reminder] Карадере, 2024";
  var message = "<p>Таблица с нещата, които още не са свършени. Можеш да тормозиш отговорниците:</p><table border='1' style='border-collapse:collapse;'><tr><th>Елемент</th><th>Количество</th><th>Приоритет</th><th>Отговорник</th><th>Статус</th><th>Забележка/коментар</th></tr>";
  var additionalMessage = "<p>Таблица с нещата, които нямат отговорник:</p><table border='1' style='border-collapse:collapse;'><tr><th>Елемент</th><th>Количество</th><th>Приоритет</th><th>Отговорник</th><th>Статус</th><th>Забележка/коментар</th></tr>";
  var hasItemsToRemind = false;
  var hasUnassignedItems = false;

  for (var i = 1; i < data.length; i++) {
    var status = data[i][4].toLowerCase();
    if (status !== "готово" && status !== "избери") {
      hasItemsToRemind = true;
      var rowColor = "";
      if (status === "не е готово") {
        rowColor = "style='background-color:#f8d7da'"; 
      } else if (status === "в процес") {
        rowColor = "style='background-color:#fff3cd'"; 
      }
      
      message += "<tr " + rowColor + ">";
      for (var j = 0; j < data[i].length; j++) {
        message += "<td>" + data[i][j] + "</td>";
      }
      message += "</tr>";
    }

    // Check for content in "Елемент" (column A) and empty "Отговорник" (column D)
    if (data[i][0] && !data[i][3]) {
      hasUnassignedItems = true;
      additionalMessage += "<tr>";
      for (var j = 0; j < data[i].length; j++) {
        additionalMessage += "<td>" + data[i][j] + "</td>";
      }
      additionalMessage += "</tr>";
    }
  }

  message += "</table>";
  additionalMessage += "</table>";

  if (hasItemsToRemind || hasUnassignedItems) {
    var finalMessage = message;
    if (hasUnassignedItems) {
      finalMessage += additionalMessage;
    }

    MailApp.sendEmail({
      to: email,
      subject: subject,
      htmlBody: finalMessage
    });
  }
}

function createDailyTrigger() {
  ScriptApp.newTrigger('checkStatusAndSendEmail')
    .timeBased()
    .atHour(9) 
    .everyDays(1)
    .create();
}

По този начин ще се добави и таблица, в която ще има списък с всичко, за което няма назначен отговорник. Имейлът, който ще се получава всяка сутрин вече изглежда така: