Лежу я ночью, пытаюсь уснуть. И как обычно тысяча мыслей, и среди них я сумел зацепился за одну. А звучала она так: "почему бы не сделать анализатора футбольных матчей, где нужно будет лишь ввести участников игры и получить выборку из их статистики общей и какие-то описание, чего ждать в грядущем матче". Действительно, почему нет?!
На следующий день, когда я проснулся, я первым же делом пошел гуглить, есть ли такое сейчас, потому что что-то подсказывало, что в нашем мире это слишком банальная затея (та ладна!?). Но я то не просто хотел складывать и делить числа из статистики, мне хотелось учитывать различные факторы спортивного мероприятия, а их большое количество.
Гугл дал свой результат, впрочем как всегда. Я нашел кучу калькуляторов ставок, которые продается за 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 здесь.
В двух словах на спортс.ру расписал, что я планировал учитывать в расчетах и как считать.
