Разместить здесь вашу рекламу


Google Sheets — как разноплановый помощник для непростых задач или как я делал анализатор футбольный матчей

Моя цель - предложение широкого ассортимента товаров и услуг на постоянно высоком качестве обслуживания по самым выгодным ценам.

Лежу я ночью, пытаюсь уснуть. И как обычно тысяча мыслей, и среди них я сумел зацепился за одну. А звучала она так: "почему бы не сделать анализатора футбольных матчей, где нужно будет лишь ввести участников игры и получить выборку из их статистики общей и какие-то описание, чего ждать в грядущем матче". Действительно, почему нет?!

На следующий день, когда я проснулся, я первым же делом пошел гуглить, есть ли такое сейчас, потому что что-то подсказывало, что в нашем мире это слишком банальная затея (та ладна!?). Но я то не просто хотел складывать и делить числа из статистики, мне хотелось учитывать различные факторы спортивного мероприятия, а их большое количество.

Гугл дал свой результат, впрочем как всегда. Я нашел кучу калькуляторов ставок, которые продается за 3-5к рублей, и прочие таблицы расчетов в свободном доступе. Я как бы и так помнил расчеты тоталов голов, но мне нужно было их улучшить и получить на выходе собственно целого "мага/колдуна/вангу" спортивных событий. Или хотя бы формулку, которая выдаст результат после ввода данных.

Это что, писать парсер?!

Мне не хотелось сильно углубляться в код. Во-первых, я не кодер, а скорее человек, который с ним постоянно сталкивается в работе, и совсем чуть-чуть в нем может разобраться. Во-вторых, мне просто было лень, я искал простые решения. И вспомнил, что чудо Google Sheets может парсить таблички, xml, html-страницы, и делается это прост формулами: IMPORTDATA, IMPORTFEED, IMPORTHTML, IMPORTXML. Вот ссылка на справку гугла, там все подробно описано, останавливаться на этом я пожалуй не буду.

Нашел источник футбольной статистики, что было очень сложно. Ведь мне нужно не только спарсить разок, а обновлять мои данные постоянно, поскольку футбольные матчи идут и идут, и данные нужно актуализировать. Остановился на зарубежном сборище футбольной инфы fbref.com, все в некрасивых таблицах 2002 года. "Как раз то, что мне нужно!", - вскрикнул я, после 3-его часа ресерча источника статистических данных. Ведь мне нужны были не простые, а всякие XG, XGa и прочие радости профессиональных футбольных "аналистов". Далее с помощью API Google Sheets и query запросов, по сути урезанным sql, я кидался данными из вкладки во вкладку, разбивая на те таблицы, которые мне будут нужны для расчетов.

Секунду, а как я инфу из Google Sheets на сайте смогу отобразить?!

Да, этот вопрос у меня появился после прекрасных дней ковыряния в данных и структурирования всей информации, которую я сумел спарсить. И я чутка приуныл, потому что помнил, что могу вывести айфреймом. Но, черт возьми, это так некрасиво и попахивает прошлым веком. Пришлось ковыряться дальше. Блог, куда я хотел это все засунуть, у меня стоит на обыкновенном Wordpress, но найти адекватный плагин, который выводил бы инфу в красивом виде на страницу ультра-сложно, чтобы ещё и работал нормально адекватно, конечно. В итоге, я нашел, даже с эстетикой выводимых таблиц я смирился. Взял плагин Inline Google Spreadsheet Viewer. Банальный до нельзя, но все же, мои таблички по крайней мере выглядели не совсем стыдно:

Пфф, я что не смогу найти скрипт для отправки данных в Google Sheets?

Не смогу. Потрачено кучу времени на поиск, весь стэкоферфлоу и гитхаб русскоязычный, англоязычный, все перерыл вдоль и поперёк. Думал я =). А оказалось, что я был рядом с решением моей проблемы. Проблема заключалась в следующем: нужно было дать возможность выбора футбольных команд пользователю, даже если это буду я (ибо трафика на блоге особо нет, да и я не парюсь), и при этом, отправить их в Google Sheets по API. Что оказалось не совсем легко.

Решение моей проблемы было у меня под носом. На одной из тысячи просмотренных мною страниц был заголовок "Отправка на почту через html форму, используя Google Apps". Но как и в других 999 страниц, я подумал, что это не имеет отношения ко мне, а ведь я был не прав.

В этой ветке было повествование о том, как отправить данные из формы на почту, при этом храня инфу в Google Sheets, что я каким-то образом упустил. И я решил попробовать обрезать в этом длинном пути к таблице почту, видь инфа с формы на почте мне точно не нужна, и куча инфы тем более. И я просто напросто так и сделал. И ничего не сломал, что было великолепным, ибо был запасной план создать левую почту для данных деяний, и засорять уже её спамом.

Ниже я добавлю весь основной код. Не знаю, могу ли публиковать не свой код, но под постом оставлю естественно ссылку на источник, ибо там есть ещё прекрасная доскональная инструкция. Да и весь код закоменчен, за что автору огромное спасибо. И Google Apps Script с этим всем справился на ура.

/******************************************************************************
 * This tutorial is based on the work of Martin Hawksey twitter.com/mhawksey  *
 * But has been simplified and cleaned up to make it more beginner friendly   *
 * All credit still goes to Martin and any issues/complaints/questions to me. *
 ******************************************************************************/

// if you want to store your email server-side (hidden), uncomment the next line
// var TO_ADDRESS = "example@email.net";

// spit out all the keys/values from the form in HTML for email
// uses an array of keys if provided or the object to determine field order
function formatMailBody(obj, order) {
  var result = "";
  if (!order) {
    order = Object.keys(obj);
  }
  
  // loop over all keys in the ordered form data
  for (var idx in order) {
    var key = order[idx];
    result += "<h4 style='text-transform: capitalize; margin-bottom: 0'>" + key + "</h4><div>" + sanitizeInput(obj[key]) + "</div>";
    // for every key, concatenate an `<h4 />`/`<div />` pairing of the key name and its value, 
    // and append it to the `result` string created at the start.
  }
  return result; // once the looping is done, `result` will be one long string to put in the email body
}

// sanitize content from the user - trust no one 
// ref: https://developers.google.com/apps-script/reference/html/html-output#appendUntrusted(String)
function sanitizeInput(rawInput) {
   var placeholder = HtmlService.createHtmlOutput(" ");
   placeholder.appendUntrusted(rawInput);
  
   return placeholder.getContent();
 }

function doPost(e) {

  try {
    Logger.log(e); // the Google Script version of console.log see: Class Logger
    record_data(e);
    
    // shorter name for form data
    var mailData = e.parameters;

    // names and order of form elements (if set)
    var orderParameter = e.parameters.formDataNameOrder;
    var dataOrder;
    if (orderParameter) {
      dataOrder = JSON.parse(orderParameter);
    }
    
    // determine recepient of the email
    // if you have your email uncommented above, it uses that `TO_ADDRESS`
    // otherwise, it defaults to the email provided by the form's data attribute
    var sendEmailTo = (typeof TO_ADDRESS !== "undefined") ? TO_ADDRESS : mailData.formGoogleSendEmail;
    
    // send email if to address is set
    if (sendEmailTo) {
      MailApp.sendEmail({
        to: String(sendEmailTo),
        subject: "Contact form submitted",
        // replyTo: String(mailData.email), // This is optional and reliant on your form actually collecting a field named `email`
        htmlBody: formatMailBody(mailData, dataOrder)
      });
    }

    return ContentService    // return json success results
          .createTextOutput(
            JSON.stringify({"result":"success",
                            "data": JSON.stringify(e.parameters) }))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(error) { // if error return this
    Logger.log(error);
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": error}))
          .setMimeType(ContentService.MimeType.JSON);
  }
}


/**
 * record_data inserts the data received from the html form submission
 * e is the data received from the POST
 */
function record_data(e) {
  var lock = LockService.getDocumentLock();
  lock.waitLock(30000); // hold off up to 30 sec to avoid concurrent writing
  
  try {
    Logger.log(JSON.stringify(e)); // log the POST data in case we need to debug it
    
    // select the 'responses' sheet by default
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheetName = e.parameters.formGoogleSheetName || "responses";
    var sheet = doc.getSheetByName(sheetName);
    
    var oldHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var newHeader = oldHeader.slice();
    var fieldsFromForm = getDataColumns(e.parameters);
    var row = [new Date()]; // first element in the row should always be a timestamp
    
    // loop through the header columns
    for (var i = 1; i < oldHeader.length; i++) { // start at 1 to avoid Timestamp column
      var field = oldHeader[i];
      var output = getFieldFromData(field, e.parameters);
      row.push(output);
      
      // mark as stored by removing from form fields
      var formIndex = fieldsFromForm.indexOf(field);
      if (formIndex > -1) {
        fieldsFromForm.splice(formIndex, 1);
      }
    }
    
    // set any new fields in our form
    for (var i = 0; i < fieldsFromForm.length; i++) {
      var field = fieldsFromForm[i];
      var output = getFieldFromData(field, e.parameters);
      row.push(output);
      newHeader.push(field);
    }
    
    // more efficient to set values as [][] array than individually
    var nextRow = sheet.getLastRow() + 1; // get next row
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

    // update header row with any new data
    if (newHeader.length > oldHeader.length) {
      sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
    }
  }
  catch(error) {
    Logger.log(error);
  }
  finally {
    lock.releaseLock();
    return;
  }

}

function getDataColumns(data) {
  return Object.keys(data).filter(function(column) {
    return !(column === 'formDataNameOrder' || column === 'formGoogleSheetName' || column === 'formGoogleSendEmail' || column === 'honeypot');
  });
}

function getFieldFromData(field, data) {
  var values = data[field] || '';
  var output = values.join ? values.join(', ') : values;
  return output;
}

Это просто спасло мне кучу времени и жизнь, ведь не реализовать как следует свою идею, это верх мучений. Как жить то потом?

Последнее, что мне оставалось, это сделать возможность автоматического обновления таблиц, дабы иметь актуальную информацию. Это уже было очень просто.

function update() { 
  var sheetName1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tournament"); 
  var sheetName2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TheMeets"); 
  var sheetName3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TheMeets_sort"); 
  var cellFunction1 = '=IMPORTHTML("https://fbref.com/en/comps/12/La-Liga-Stats","table",1)';
  var cellFunction2 = '=sort({IMPORTHTML("https://fbref.com/en/comps/12/schedule/La-Liga-Scores-and-Fixtures","table",1);IMPORTHTML("https://fbref.com/en/comps/12/3239/schedule/2019-2020-La-Liga-Scores-and-Fixtures","table",1);IMPORTHTML("https://fbref.com/en/comps/12/1886/schedule/2018-2019-La-Liga-Scores-and-Fixtures","table",1);IMPORTHTML("https://fbref.com/en/comps/12/1652/schedule/2017-2018-La-Liga-Scores-and-Fixtures","table",1)},3,FALSE)';
  var cellFunction3 = '=sort(IMPORTHTML("https://fbref.com/en/comps/12/schedule/La-Liga-Scores-and-Fixtures","table",1),3,TRUE)';
  
    sheetName1.getRange('A1').setValue(cellFunction1); 
    sheetName2.getRange('A2').setValue(cellFunction2);
    sheetName3.getRange('A1').setValue(cellFunction3);
}

Выбираем таблицы и обновляем их. Так же добавляем в Google Apps Script триггер, на развертывание данного скрипта ежедневно. И вуаля!

УРА!

Я победил и смог довести дело до конца, сейчас все это выглядит в более ли менее адекватном виде:

Тут мы жмякаем на кнопку "Анализ статистики" и попадаем на страницу "Спасибо", которую я заюзал как задержку, чтобы дать возможность Google Sheets принять данные с формы, все посчитать, и выдать всю информацию. А на выходе получил красивую статистику матчей, и небольшие предсказания моей ванга-таблички.

Я надеюсь, что моя работа была проделана не зря, и в русскоязычном мире это статья спасет ещё пару жизней от манящих и поедающих тебя изнутри идей.

Весь код, инструкция и как с помощью Google Apps Script складировать свои данные в Google Sheets здесь.

В двух словах на спортс.ру расписал, что я планировал учитывать в расчетах и как считать.

Источник: https://habr.com/ru/post/552908/


Интересные статьи

Интересные статьи

Компания Google открыла исходники сканера Tsunami — решения для обнаружения опасных уязвимостей с минимальным количеством ложных срабатываний. Tsunami отличается от сотен других ска...
Несколько лет назад делал себе АВР (автоматический ввод резерва) для работы на даче от генератора. Сейчас многие ИТ-шники переходят на удалёнку, работают с дач, где качество элект...
Что первое приходит в голову разработчику инди-игры, когда он сталкивается с необходимостью добавления фичи, представления о реализации которой толком не имеет? Разумеется, он идёт искать следы т...
Привет, Хабр! Вне всяких сомнений многие из вас уже открыли велосезон (а кто-то его не закрывал) и это похвально. Мы тоже катаемся, а вместе с этим возобновляем наш блог, в котором в прошлом году...
Здравствуйте! Меня зовут Марат Гаянов, я хочу поделиться с вами моим решением задачи с конкурса Best Reverser, показать, как сделать кейген для этого кейса. Описание В этом конкурсе учас...