Töötamine onEdit päästiku (reaktsioon muudatustele tabelis) ja LanguageApp klassiga (teksti tõlkimine Google Spreadsheetsis)
Mida see kood teeb?
See skript töötab automaatselt, kui kasutaja sisestab andmeid Google Sheetsi lahtrisse.
- Käivitatakse
onEdit(e)funktsioon, kui kasutaja muudab lahtrit - Kontrollitakse, millisel lehel muudatus toimus:
- Kui lehe nimi ei ole
"Перевод текста"(Tõlketekst), siis skript peatub
- Kui lehe nimi ei ole
- Kontrollitakse, kas muudatus toimus esimeses veerus:
- Kui muudatus ei ole esimeses veerus, siis skript peatub
- Tõlgib venekeelse teksti inglise keelde:
- Loeb venekeelse teksti esimesest veerust
- Kasutab Google’i keele API-t (
LanguageApp.translate()), et tõlkida vene keelest inglise keelde
- Salvestab tõlgitud teksti teise veergu (samas reas)
Tulemus
Kohandatud koodi muutmine
Lisatud uued tõlked
- Eelmine versioon tõlkis ainult vene keelest inglise keelde
- Uus versioon tõlgib nüüd ka prantsuse (
fr), jaapani (ja) ja läti (lv) keelde
Uued muutuja nimetused
- Lisatud uued muutujad
translatedText2,translatedText3jatranslatedText4, et salvestada erinevatesse keeltesse tõlgitud tekst
Tulemuste salvestamine uutesse veergudesse
- Ingliskeelne tõlge salvestatakse 2. veergu
- Prantsusekeelne tõlge salvestatakse 3. veergu
- Jaapanikeelne tõlge salvestatakse 4. veergu
- Lätikeelne tõlge salvestatakse 5. veergu
Tulemus
Originaal kood
// Функция onEdit является триггером, который срабатывает при завершении ввода данных в ячейку Таблицы пользователем
// https://developers.google.com/apps-script/guides/triggers/#onedite
function onEdit(e) {
Logger.log(e);
// Получаем диапазон ячеек, в которых произошли изменения
// https://developers.google.com/apps-script/reference/spreadsheet/range
var range = e.range;
// Лист, на котором производились изменения
// https://developers.google.com/apps-script/reference/spreadsheet/sheet
var sheet = range.getSheet();
// Проверяем, нужный ли это нам лист
Logger.log(sheet.getName());
if (sheet.getName() != 'Перевод текста') {
return false;
}
// Переводить необходимо текст, введённый только в первую колонку.
// Проверяем стартовую позицию диапазона
Logger.log(range.getColumn());
if (range.getColumn() != 1) {
return false;
}
for (var i = 1; i <= range.getNumRows(); i++) {
var cell = range.getCell(
i, // номер строки
1 // номер колонки
);
// Получаем текст на русском
var russianText = cell.getValue();
// Переводим текст на английский
// https://developers.google.com/apps-script/reference/language/language-app
var translatedText = LanguageApp.translate(
russianText, // текст
'ru', // с какого языка переводим
'en' // на какой язык переводим
);
// Вставляем переведённый текст во вторую колонку
sheet.getRange(
cell.getRowIndex(), // номер строки
2 // номер столбца
).setValue(translatedText);
}
}
Uus kood muudatusega
// Функция onEdit является триггером, который срабатывает при завершении ввода данных в ячейку Таблицы пользователем
// https://developers.google.com/apps-script/guides/triggers/#onedite
function onEdit(e) {
Logger.log(e);
// Получаем диапазон ячеек, в которых произошли изменения
// https://developers.google.com/apps-script/reference/spreadsheet/range
var range = e.range;
// Лист, на котором производились изменения
// https://developers.google.com/apps-script/reference/spreadsheet/sheet
var sheet = range.getSheet();
// Проверяем, нужный ли это нам лист
Logger.log(sheet.getName());
if (sheet.getName() != 'Перевод текста') {
return false;
}
// Переводить необходимо текст, введённый только в первую колонку.
// Проверяем стартовую позицию диапазона
Logger.log(range.getColumn());
if (range.getColumn() != 1) {
return false;
}
for (var i = 1; i <= range.getNumRows(); i++) {
var cell = range.getCell(
i, // номер строки
1 // номер колонки
);
// Получаем текст на русском
var russianText = cell.getValue();
// Переводим текст на английский
// https://developers.google.com/apps-script/reference/language/language-app
// https://cloud.google.com/translate/docs/languages
let translatedText = LanguageApp.translate(
russianText, // текст
'ru', // с какого языка переводим
'en' // на какой язык переводим
);
let translatedText2 = LanguageApp.translate(
russianText, // текст
'ru', // с какого языка переводим
'fr' // на какой язык переводим
);
let translatedText3 = LanguageApp.translate(
russianText, // текст
'ru', // с какого языка переводим
'ja' // на какой язык переводим
);
let translatedText4 = LanguageApp.translate(
russianText, // текст
'ru', // с какого языка переводим
'lv' // на какой язык переводим
);
// Вставляем переведённый текст во вторую колонку
sheet.getRange(
cell.getRowIndex(), // номер строки
2 // номер столбца
).setValue(translatedText);
// Вставляем переведённый текст в третью колонку
sheet.getRange(
cell.getRowIndex(), // номер строки
3 // номер столбца
).setValue(translatedText2);
// Вставляем переведённый текст в четвертую колонку
sheet.getRange(
cell.getRowIndex(), // номер строки
4 // номер столбца
).setValue(translatedText3);
// Вставляем переведённый текст в четвертую колонку
sheet.getRange(
cell.getRowIndex(), // номер строки
5 // номер столбца
).setValue(translatedText4);
}
}
Тöö HTML-teenusega (modaali ja külgriba kuvamine Google Spreadsheetis)
Funktsioon showSidebar() – Sülgriba avamine
- Loob HTML-koodi külgriba (sidebar) jaoks:
- Lisab Bootstrapi ja jQuery
- Loob HTML-vormi järgmiste väljadega:
- Nimi (
name) - Tekstiväli (
abrakadabra) - Reanumber (
strNum)
- Nimi (
- Lisab kaks nuppu:
- “Salvesta andmed tabelisse” – saadab andmed Google Sheetsi
- “Sulge külgriba” – sulgeb külgriba
- Lisab JavaScripti koodi sündmuste töötlemiseks:
- Vormis sisestatud andmed saadetakse funktsioonile
writeStrInTable(), mis salvestab need tabelisse - Kui vajutada nuppu “Sulge külgriba”, siis paneel suletakse
- Vormis sisestatud andmed saadetakse funktsioonile
- Avab külgriba Google Sheetsi aknas
Funktsioon writeStrInTable(e) – Andmete salvestamine tabelisse
- Võtab vormist saadud andmed (nimi, tekst, reanumber)
- Leiab aktiivse töölehe Google Sheetsis
- Salvestab andmed õigesse ritta:
- Nimi → veerg
A. - Tekst → veerg
B.
- Nimi → veerg
- Tagastab eduteate (“Kõik õnnestus!”)
Tulemus
Kohandatud koodi muutmine
Lisatud uus küsimus vormi
- Lisatud küsimus: “Вам нравится данная форма?”
- Kasutaja saab valida “Да” või “Нет”, kasutades raadionuppe (
<input type="radio">)
Uue küsimuse vastuse edastamine
- JavaScriptis lisatud raadionuppude väärtuse (
jahEi) saatmine funktsioonilewriteStrInTable( - Kasutatakse jQuery-d
$('input[name=jahEi]:checked').val(), et leida valitud vastus
Andmete salvestamine Google Sheetsi
- Funktsioon
writeStrInTable(e)võtab vastu uue muutujaradioNupp, mis sisaldab kasutaja valikut (“Да” või “Нет”) - See väärtus lisatakse uude veergu C (
sheet.getRange("C" + strNum).setValue(radioNupp))
Tulemus
Originaal kood
// Открытие сайдбара
function showSidebar() {
// Подключаем Bootstrap
var sidebarHTML = '<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">';
// Подключаем jQuery
sidebarHTML += '<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>';
// Создаём форму
sidebarHTML += '<form style="padding: 20px;text-align:center;">
<div class="form-group">
<label for="name">Имя</label>
<input type="text" class="form-control" id="name" name="name" value="">
</div>
<div class="form-group">
<label for="abrakadabra">Какая-то абракадабра</label>
<textarea class="form-control" id="abrakadabra" name="abrakadabra" rows="3"></textarea>
</div>
<div class="form-group">
<label for="strNum">Номер строки, в которую необходимо внести данные</label>
<input type="text" class="form-control" id="strNum" name="strNum" value="">
</div>
<button type="submit" class="btn btn-primary">Записать данные в таблицу</button>
<br><br><br>
<button type="button" id="sidebarClose" class="btn btn-danger">Закрыть сайдбар</button>
</form>';
// Добавляем скрипты
// При сабмите формы вызываем функцию writeStrInTable() и передаём ей введённые данные
// https://developers.google.com/apps-script/guides/html/reference/run
// При клике на кнопку 'Закрыть сайдбар' закрываем его
// https://developers.google.com/apps-script/guides/html/reference/host#close()
sidebarHTML += "<script>
$(document).on('submit', 'form', function () {
google.script.run
.withSuccessHandler(function (resultMsg) {
alert(resultMsg);
})
.writeStrInTable(
{ name: $('#name').val(), abrakadabra: $('#abrakadabra').val(), strNum: $('#strNum').val() }
);
return false;
});
$('#sidebarClose').on('click', function() {
google.script.host.close();
});
</script>";
var htmlOutput = HtmlService
.createHtmlOutput(sidebarHTML)
.setTitle('My add-on');
// https://developers.google.com/apps-script/reference/base/ui#showsidebaruserinterface
SpreadsheetApp.getUi().showSidebar(htmlOutput);
}
function writeStrInTable(e) {
var name = e.name;
var abrakadabra = e.abrakadabra;
var strNum = parseInt(e.strNum);
// Получаем объект с активной (открытой в данный момент) таблицей
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
// Записываем полученные данные в таблицу
sheet.getRange("A" + strNum).setValue(name);
sheet.getRange("B" + strNum).setValue(abrakadabra);
return "Всё прошло успешно!";
}
Uus kood muudatusega
// Открытие сайдбара
function showSidebar() {
// Подключаем Bootstrap
var sidebarHTML = '<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">';
// Подключаем jQuery
sidebarHTML += '<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>';
// Создаём форму
sidebarHTML += '<form style="padding: 20px;text-align:center;">
<div class="form-group">
<label for="name">Имя</label>
<input type="text" class="form-control" id="name" name="name" value="">
</div>
<div class="form-group">
<label for="abrakadabra">Какая-то абракадабра</label>
<textarea class="form-control" id="abrakadabra" name="abrakadabra" rows="3"></textarea>
</div>
<div class="form-group">
<label for="strNum">Номер строки, в которую необходимо внести данные</label>
<input type="text" class="form-control" id="strNum" name="strNum" value="">
</div>
<div class="form-group">
<label>Вам нравится данная форма?</label><br>
<input type="radio" id="yes" name="jahEi" value="да">
<label for="yes">Да</label><br>
<input type="radio" id="no" name="jahEi" value="нет">
<label for="no">Нет</label><br>
</div>
<button type="submit" class="btn btn-primary">Записать данные в таблицу</button>
<br><br><br>
<button type="button" id="sidebarClose" class="btn btn-danger">Закрыть сайдбар</button>
</form>';
// Добавляем скрипты
sidebarHTML += "<script>
$(document).on('submit', 'form', function () {
google.script.run
.withSuccessHandler(function (resultMsg) {
alert(resultMsg);
})
.writeStrInTable(
{ name: $('#name').val(), abrakadabra: $('#abrakadabra').val(), strNum: $('#strNum').val(), jahEi: $('input[name=jahEi]:checked').val() }
);
return false;
});
$('#sidebarClose').on('click', function() {
google.script.host.close();
});
</script>";
var htmlOutput = HtmlService
.createHtmlOutput(sidebarHTML)
.setTitle('My add-on');
SpreadsheetApp.getUi().showSidebar(htmlOutput);
}
function writeStrInTable(e) {
var name = e.name;
var abrakadabra = e.abrakadabra;
var strNum = parseInt(e.strNum);
var radioNupp = e.jahEi;
// Получаем объект с активной (открытой в данный момент) таблицей
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
// Записываем полученные данные в таблицу
sheet.getRange("A" + strNum).setValue(name);
sheet.getRange("B" + strNum).setValue(abrakadabra);
sheet.getRange("C" + strNum).setValue(radioNupp);
return "Всё прошло успешно!";
}
Veebisaitide pealkirjade parsimine
Mida see kood teeb?
Avab Google’i tabeli
- Leiab lehe nimega
"list" - Võtab esimese veeru URL-id (veebilehtede aadressid)
Läbib URL-ide nimekirja
- Võtab ühe lingi ja saadab sellele päringu
- Kui sait ei avane, kirjutab tabelisse vea
- Kui sait avaneb, kontrollib lehe kodeeringut
Otsib lehe pealkirja (<title>)
- Kui pealkiri leitakse, kirjutab selle teise veergu
- Kui pealkirja ei leita, kirjutab tabelisse vea
Kordab seda protsessi kõigi linkide jaoks
Tulemus
Kohandatud koodi muutmine
Lisatud uus päring (footer)
- Lisatud kood, mis otsib lehelt
footerosa, kasutades regulaaravaldist (/<footer[^>]*>([\s\S]*?)<\/footer>/i) - Kui
footerleiti, eemaldatakse kõik HTML sildid ja saadakse puhas tekst, mis siis salvestatakse Google Sheetsi. (footer.replace(/<[^>]*>/g, '').trim();) - Täiendavalt on lisatud kood, mis asendab kõik
©HTML entiteedid sümboliga©: (footerText.replace(/©/g, '©'); )
Kasutaja määratud veeru haldamine
- Kui lehelt leiti
footer, siis selle tekst lisatakse uude veergu 3 (koodissheet.getRange(i + 1, 3).setValue(footerText))
Muudetud veateade funktsioon
- Funktsioon
insertErroron muudetud, et ta saaks lisada veateate mitte ainult veergu 2, vaid ka veergu 3- Parameeter
numColumnon lisatud, et määrata veerg, kuhu veateade kirjutatakse
- Parameeter
Täiendavad kontrollid ja veateated
- Kui
titlevõifooterei leita, siis kirjutatakse veateade vastavatesse veergudesse (veerg 2 ja veerg 3)
Tulemus
Originaal kood
function parser() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('list');
var values = sheet.getDataRange().getValues();
var insertError = function(msg, numRow) {
sheet.getRange(numRow, 2).setValue('ОШИБКА! ' + msg).setBackground('red');
}
var rowNumStart = 1; // Номер строки, с которой необходимо начать парсинг
for (var i = rowNumStart; i < values.length; i++) {
var url = values[i][0];
try {
var response = UrlFetchApp.fetch(url, {
'muteHttpExceptions': true,
'validateHttpsCertificates': false,
'followRedirects': true,
}); // Загружаем страницу по указанному url
} catch(e) {
insertError(e, i+1);
continue;
}
// https://developers.google.com/apps-script/reference/url-fetch/http-response
var responseCode = response.getResponseCode(); // Код ответа сервера
if (responseCode === 200) {
try {
var headers = response.getHeaders();
var charset = headers['Content-Type'].match(/charset=(.*)$/)[1].replace(/[";]+/gi, ''); // Кодировка страницы
} catch (e) {
var contentForCharset = response.getContentText();
var charsetMatch = contentForCharset.match(/charset=(.*?)"/);
var charset = (charsetMatch !== null) ? charsetMatch[1] : "UTF-8";
}
var content = response.getContentText(charset); // Получаем html код страницы
// Получаем название поста
var titleMatch = content.match(/<title[^>]*>\s*([\s\S]*?)\s*</);
if (titleMatch !== null) {
var title = titleMatch[1];
// Вставляем название в таблицу
sheet.getRange(i+1,2).setValue(title);
} else {
insertError('Не удалось распарсить title.', i+1);
}
} else {
insertError('Код ответа сервера != 200.', i+1);
}
}
}
Uus kood muudatusega
function parser() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('list');
var values = sheet.getDataRange().getValues();
var insertError = function(msg, numRow, numColumn) {
sheet.getRange(numRow, numColumn).setValue('ОШИБКА! ' + msg).setBackground('red');
}
var rowNumStart = 1; // Номер строки, с которой необходимо начать парсинг
for (var i = rowNumStart; i < values.length; i++) {
var url = values[i][0];
try {
var response = UrlFetchApp.fetch(url, {
'muteHttpExceptions': true,
'validateHttpsCertificates': false,
'followRedirects': true,
}); // Загружаем страницу по указанному url
} catch(e) {
insertError(e, i+1, 2);
continue;
}
// https://developers.google.com/apps-script/reference/url-fetch/http-response
var responseCode = response.getResponseCode(); // Код ответа сервера
if (responseCode === 200) {
try {
var headers = response.getHeaders();
var charset = headers['Content-Type'].match(/charset=(.*)$/)[1].replace(/[";]+/gi, ''); // Кодировка страницы
} catch (e) {
var contentForCharset = response.getContentText();
var charsetMatch = contentForCharset.match(/charset=(.*?)"/);
var charset = (charsetMatch !== null) ? charsetMatch[1] : "UTF-8";
}
var content = response.getContentText(charset); // Получаем html код страницы
// Получаем название поста
var titleMatch = content.match(/<title[^>]*>\s*([\s\S]*?)\s*</);
var footerMatch = content.match(/<footer[^>]*>([\s\S]*?)<\/footer>/i);
if (titleMatch !== null) {
var title = titleMatch[1];
// Вставляем название
sheet.getRange(i + 1, 2).setValue(title);
} else {
insertError('Не удалось распарсить title', i + 1, 2);
}
if (footerMatch !== null) {
var footer = footerMatch[1];
// Убираем все HTML теги из текста футера
// Источник https://www.w3schools.com/jsref/jsref_trim_string.asp
var footerText = footer.replace(/<[^>]*>/g, '').trim();
footerText = footerText.replace(/©/g, '©');
sheet.getRange(i + 1, 3).setValue(footerText);
} else {
insertError('Не удалось распарсить footer', i + 1, 3);
}
} else {
insertError('Код ответа сервера != 200', i + 1, 2);
}
}
}