I lie at night, trying to sleep. And as usual a thousand thoughts, and among them I managed to catch one. And it sounded like this: "why not make an analyzer of football matches, where you just need to enter the participants in the game and get a sample of their general statistics and some description of what to expect in the upcoming match." Indeed, why not ?!
The next day, when I woke up, the first thing I did was google, is there such a thing now, because something suggested that in our world this is too trivial an idea (okay !?). But I didn't just want to add and divide numbers from statistics, I wanted to take into account various factors of a sporting event, but there are a large number of them.
Google gave its result, however, as always. I found a bunch of rate calculators that are sold for 3-5k rubles, and other calculation tables in the public domain. I kind of remembered the calculations of total goals, but I needed to improve them and get a whole "magician / sorcerer / wangoo" of sports events at the output. Or at least a formula that will return the result after entering the data.
Is this writing a parser ?!
I didn't want to go deep into the code. Firstly, I am not a coder, but rather a person who constantly encounters him in his work, and can understand him quite a bit. Secondly, I was just too lazy, I was looking for simple solutions. And I remembered that the miracle of Google Sheets can parse tables, xml, html pages, and this is done with simple formulas: IMPORTDATA, IMPORTFEED, IMPORTHTML, IMPORTXML. Here is a link to Google's help, everything is described in detail there, I probably won't dwell on this.
, . , , , . 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 , , . , -.
I hope that my work was not done in vain, and in the Russian-speaking world, this article will save a couple more lives from ideas that are tempting and eating you from the inside.
All the code, instructions and how to use Google Apps Script to store your data in Google Sheets here .