SQUIZ

Обзор

Нужно очистить от формул столбцы с I по R (sent_date - experience)
Потому что скрипт опирается на пустоту ячеек и таким образом понимает откуда до куда надо считать

При первом прогоне могут быть таймауты:

Exceeded maximum execution time

Это нормально, на работу скрипта выделяется ~10мин, этого хватает для обработки ~20к строк
Нужно просто запустить расчет снова и он продолжит оттуда где остановился
Сперва высчитываются столбцы с sent_date, по lead_number, это происходит достаточно быстро (там простая логика)
После этого начинают считаться оставшиеся столбцы (это занимает больше времени)

В моем случае за два запуска обработалась таблица на ~50к строк

Как добавить скрипт в таблицу

• Переходим Extensions > Apps Script
  

• Открывается новая вкладка с редактором кода
  

• Вставляем туда код описанный в самом низу этой страницы и сохраняем:
  

• Как сохранится - вкладку можно закрывать
  После сохранения кода нужно обязательно обновить страницу с таблицей, чтобы новый код подгрузился в открытую таблицу
  Убеждаемся что столбцы с I по R - пустые
  И нажимаем Пересчет значений > Досчитать новые и ждем...
  

• Появится всплывашка "Running script"
  

• Спустя время заполняется первая группа столбцов
  

• Затем остальные
  

• Если есть ощущение что все зависло, то можно перезагрузить страницу

Скрипт

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Пересчет значений').addItem('Досчитать новые', 'main').addToUi();
}

function main() {
  var debug = false; // Переключить в "true" для отображения отладочных сообщений
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('db');
  var ui = SpreadsheetApp.getUi();
  var batch_size = 1000;

  // Кол-во строк в документе (номер последней строки)
  var document_size = sheet.getLastRow();

  // Последняя не пустая строка в столбце "I" ('sent_date')
  // Из таблицы считывается диапазон "A2:I<max>",
  // данные заполняются человеком до столбца "H" включительно,
  // а по состоянию стобца "I" мы сможем понять до какой строки уже вычислена таблица
  var values = sheet.getRange("A2:I" + document_size).getValues();

  if (debug) {
    // spreadsheet.toast("Calculating...");
    ui.alert(values);
    ui.alert("values.length=" + values.length);
  }

  var last_complete_line_index = 0; // В 0-ой строке заголовки столбцов
  for (var i = (values.length - 1); i >= 0; i--) {
    if (values[i][8] !== "") { // Проверяем каждый элемент 8-ого столбца на пустоту (это столбец "I" - "sent_date")
      last_complete_line_index = i;
      break;
    }
  }

  if (debug) {
    // spreadsheet.toast("Calculating...");
    ui.alert("document_size=" + document_size);
    ui.alert("last_complete_line_index=" + last_complete_line_index);
  }

  // Если последняя строка таблицы это последняя посчитанная строка, то ничего досчитывать не нужно
  if (last_complete_line_index == document_size) {
    spreadsheet.toast("Новых значений нет");
    return;
  }

  // Массив для хранения вычисленных значений столбцов от "I" до "M"
  var results_from_I_to_N = [];
  var results_from_I_to_N_start_index = last_complete_line_index;

  if (debug) {
    // spreadsheet.toast("Calculating...");
    ui.alert("Начинается перебор...");
  }
  // Перебор значений от last_complete_line_index+1 до последней строки (цикл по добавленным строкам)
  for (var i = last_complete_line_index; i < (document_size - 1); i++) {
    results_from_I_to_N.push(
      [
        values[i][1].toString().substring(0, 10), // sent_date: Берем первые 10 символов из 1-ого столбца i-ой строки (столбец "B")
        values[i][1].toString().substring(0, 7), // sent_month: Аналогично первые 7 символов
        values[i][3].toString().toLowerCase().replaceAll(" ", ""), // email_format: Из 3-его столбца ("D") убираем пробелы
        values[i][4].toString().toLowerCase()
          .replaceAll("+", "")
          .replaceAll("(", "")
          .replaceAll(")", "")
          .replaceAll("-", "")
          .replaceAll(" ", ""), // phone_format: Из 4-его столбца ("E") убираем ненужные символы
        values[i][6].toString().toLowerCase()
          .replaceAll("ё", "е")
          .replaceAll("!", "")
          .replaceAll("?", "")
          .replaceAll(",", "")
          .replaceAll("-", "")
          .replaceAll(".", "")
          .replaceAll("‥", "")
          .replaceAll("…", "")
          .replaceAll("#", "")
          .replaceAll('"', "")
          .replaceAll(" ", ""), // team_format: Из 6-ого столбца ("G") убираются все эти символы
        i + 1 // lead_number: В столбец "N" просто проставляется порядковый номер строки
      ]
    )
  }

  if (debug) {
    // spreadsheet.toast("Calculating...");
    ui.alert(results_from_I_to_N);
    ui.alert("setValues range: " + "I" + last_complete_line_index + ":N" + document_size);
  }

  // Запись первых 6-и вычисленных столбцов (от "I" до "N")
  try {
    sheet.getRange("I" + (last_complete_line_index + 2) + ":N" + document_size).setValues(results_from_I_to_N)
  } catch(e) {
    SpreadsheetApp.getUi().alert("Error writing values: " + e.message);
  }
  

  //     ---==============================================---
  // ---=== first_lead / client_id / activity / experience ===---
  //     ---==============================================---

  last_complete_line_index = 0;
  for (var i = (values.length - 1); i >= 0; i--) {
    if (values[i][14] !== "" && values[i][14]) { // Проверяем каждый элемент 14-ого столбца на пустоту (это столбец "O" - "first_lead")
      last_complete_line_index = i;
      break;
    }
  }
  if (debug) {
    ui.alert("last_complete_line_index=" + last_complete_line_index);
    ui.alert("document_size=" + document_size);
  }
  // Если последняя строка таблицы это последняя посчитанная строка, то ничего досчитывать не нужно
  if (last_complete_line_index == document_size) {
    spreadsheet.toast("Новых значений нет");
    return;
  }

  // Из таблицы считывается диапазон "I2:N<max>"
  var values = sheet.getRange("A2:O" + document_size).getValues();
  var values_from_O_to_R = sheet.getRange("O2:R" + (last_complete_line_index + 2)).getValues(); // +2 потому что счет с 0 и в первой строке заголовки
  var values_first_lead = [];
  var values_client_id = [];
  var values_sent_date = [];
  if (values_from_O_to_R.length > 1) {
    for (row of values_from_O_to_R) {
      values_first_lead.push(row[0]);
      values_client_id.push(row[1]);
    }
    for (row of values) {
      values_sent_date.push(row[8]);
    }
  }

  var emails = [];
  var phones = [];
  var request_types = [];
  var signup_count_by_client_id = new Array(last_complete_line_index).fill(0); // Индекс ячейки это client_id, значение это количество signup'ов
  for (row of values) {
    emails.push(row[10]); // Массив emails наполняется значениями столбца "K"
    phones.push(row[11]); // Массив phones наполняется значениями столбца "L"
    request_types.push(row[2]); // Массив request_types наполняется значениями столбца "C"
  }

  var client_ids = [];
  if (values_from_O_to_R.length > 1) {
    for (row of values_from_O_to_R) {
      client_ids.push(row[1]);
      if (row[2] != "") {
        signup_count_by_client_id[row[1]] += 1;
      }
    }
  }
  var client_id_max =  client_ids.length > 1 ? Math.max(...client_ids) : 1;

  // Массив для хранения вычисленных значений столбцов от "O" до "R"
  var results_from_O_to_R = [];

  // Перебор значений от last_complete_line_index+1 до последней строки (цикл по добавленным строкам)
  if (debug) {
    ui.alert("last_complete_line_index=" + last_complete_line_index);
    ui.alert("document_size=" + document_size);
    ui.alert("values=" + values);
    ui.alert("values_from_O_to_R=" + values_from_O_to_R);
    ui.alert("values_from_O_to_R.length=" + values_from_O_to_R.length);
  }
  
  for (var i = last_complete_line_index; i < (document_size - 1); i++) {
    var first_lead = "";
    var client_id = "";
    var activity = "";
    var experience  = "";
    if (!values[i] || !values[i][10] || !values[i][11]) {
      results_from_O_to_R.push(
        [
          first_lead, // first_lead: Выбирается минимум из двух значений: первое вхождение почты в почтах и телефона в телефонах
          client_id, // client_id: По порядку увеличивающийся номер
          activity, // activity: 
          experience // experience:
        ]
      )
      continue;
    }
    var first_lead_email_index = emails.indexOf(values[i][10].toString());
    var first_lead_phone_index = phones.indexOf(values[i][11].toString());
    if (first_lead_email_index == -1) {
      first_lead = first_lead_phone_index + 1;
    } else if (first_lead_phone_index == -1) {
      first_lead = first_lead_email_index + 1;
    } else {
      first_lead = Math.min(first_lead_email_index, first_lead_phone_index) + 1;
    }

    if (first_lead != values[i][13]) { // Проверка что first_lead не равен lead_number
      var values_first_lead_indexOf = values_first_lead.indexOf(first_lead)
      client_id = values_first_lead_indexOf > 0 ? values_client_id[values_first_lead_indexOf] : 0;
    } else {
      client_id = client_id_max;
      client_id_max++;
    }

    values_first_lead.push(first_lead);
    values_client_id.push(client_id);
    if (!signup_count_by_client_id[client_id]) {
      signup_count_by_client_id[client_id] = 0;
    }

    if (request_types[i] == "signup") { // Если request_type "signup", то вычислить ячейку, в ином случае она остается пустой
      try {
        var i_results_index = i - results_from_I_to_N_start_index;
        if (results_from_I_to_N[i_results_index]) {
          var sent_date_Date = new Date(results_from_I_to_N[i_results_index][0].substring(0, 10));
        } else {
          var sent_date_Date = new Date(values[i][8]); // fallback — берём из исходной таблицы
        }
      } catch(e) {
        var sent_date_Date = new Date(results_from_I_to_N[i - last_complete_line_index][0]);
      }
      
      var sent_date_epoch_days = Math.floor(sent_date_Date.getTime() / (24 * 60 * 60 * 1000)); // 1000 милисекунд, 60 секунд, 60 минут, 24 часа = 1 сутки
      if (first_lead == values[i][13]) { // Проверка что first_lead равен lead_number. Значит это первая игра и activity=new
        activity = "new";
      } else { // Иначе у игрока уже были игры, значит вычисляем как давно была предыдущая игра
        var previous_sent_date_index = values_client_id.lastIndexOf(client_id, i - 1);
        if (previous_sent_date_index !== -1) { // Проверяем, что нашли предыдущую запись
          try {
            var previous_sent_date_Date = new Date(values_sent_date[previous_sent_date_index].substring(0, 10));
          } catch(e) {
            var previous_sent_date_Date = new Date(values_sent_date[previous_sent_date_index]);
          }
          var previous_sent_date_epoch_days = Math.floor(previous_sent_date_Date.getTime() / (24 * 60 * 60 * 1000));
          var days_diff = sent_date_epoch_days - previous_sent_date_epoch_days;
          if (days_diff > 120) {
            activity = "reactivated";
          } else {
            activity = "active";
          }
        }
      }
      
      //experience = "junior";
      if (signup_count_by_client_id[client_id] > 26) {
        experience = "senior";
      } else if (signup_count_by_client_id[client_id] > 5) {
        experience = "middle";
      } else {
        experience = "junior";
      }
      signup_count_by_client_id[client_id] += 1;
    }

    results_from_O_to_R.push(
      [
        first_lead, // first_lead: Выбирается минимум из двух значений: первое вхождение почты в почтах и телефона в телефонах
        client_id, // client_id: По порядку увеличивающийся номер
        activity, // activity: 
        experience // experience:
      ]
    )
    
  }
  try {
    sheet.getRange("O" + (last_complete_line_index + 2) + ":R" + document_size).setValues(results_from_O_to_R)
  } catch(e) {
    SpreadsheetApp.getUi().alert("Error writing values: " + e.message);
  }
}