Блог
Бесконечные Зависимые Выпадающие Списки в Google Sheets (часть 2)
Данная статья является продолжением статьи Бесконечные Зависимые Выпадающие Списки в Google Sheets.
Здесь описаны изменения изменения и дополнения кода, а именно:
- Автоматическое создание листов Номе Data_2;
- Автоматическое добавление всех формул на листе Номе;
- При редактировании созданной ранее строки зависимых (связанных списков), автоматически удаляются значения и формулы справа от редактируемой ячейки.
function onEdit(e) {
var row = e.range.getRow();
var col = e.range.getColumn();
var list_name = e.source.getActiveSheet().getName();
var name = e.value;
var oldName = e.oldValue;
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
var mask = JSON.stringify(sh.getRange(row, 1, 1, col).getValues()[0]);
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
var colMax = sh.getLastColumn();
if(list_name === "Home" && name !== oldName && col < colMax) {
fillColumn(row, col, mask);
}
}
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Custom Menu')
.addItem('Create sheets', 'createSheets')
.addToUi();
}
function fillColumn(row, col, mask) {
// clear dataVal and Value
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
var colMax = sh.getLastColumn();
sh.getRange(row, col + 1, 1, colMax).clearDataValidations().clearContent();
// find date
var sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var arrData = sd.getDataRange().getValues();
var arrData_2 = [];
arrData_2.push(arrData[0]);
var iMax = arrData.length - 1;
for(var i=1; i<=iMax; i++) {
if(JSON.stringify(arrData[i].slice(0, col)) == mask) {
arrData_2.push(arrData[i]);
}
}
// clear Data_2
var sd_2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data_2");
sd_2.getDataRange().clearContent();
// insert data
sd_2.getRange(1, 1, arrData_2.length, arrData_2[0].length).setValues(arrData_2);
// add dataVal
col++;
var list = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K'];
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
sh.getRange(row, col).setDataValidation(SpreadsheetApp.newDataValidation()
.setAllowInvalid(false)
.requireValueInRange(sh.getRange('Data_2!$' + list[col - 1] + '$2:$' + list[col - 1] + '$1000'), true)
.build());
}
function createSheets() {
// is exist Home?
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sd = ss.getSheetByName('Data')
// create if not exist
if(!ss.getSheetByName('Home')) {
ss.insertSheet('Home', 0);
// create Data Val
var sh = ss.getSheetByName('Home');
sh.getRange('Home!A2:A20').setDataValidation(SpreadsheetApp.newDataValidation()
.setAllowInvalid(false)
.requireValueInRange(sh.getRange('Data!$A$2:$A'), true)
.build());
sh.getRange(1, 1, 1, 10).setValues(sd.getRange(1, 1, 1, 10).getValues()).setFontWeight('bold');
};
// is exist Data_2?
if(!ss.getSheetByName('Data_2')) {
// create if not exist
var k = ss.getNumSheets();
ss.insertSheet('Data_2', k + 1);
var sd_2 = ss.getSheetByName('Data_2');
sd_2.getRange(1, 1, 1, 10).setValues(sd.getRange(1, 1, 1, 10).getValues()).setFontWeight('bold');
};
}Теперь, в этой версии программы появилась возможность по нажатию всего лишь одной(!) кнопки из пользовательского меню автоматически создавать все листы файла, необходимые для работы скрипта (включая форматирование и валидацию данных).
Всё что для этого нужно:
- cоздать лист с именем "Data"
- скопировать в него данные, необходимые для связанных (зависимых) списков
- скопировать и встваить в файл этот скрипт
- обновить страницу
- в появившемся пользовательском меню выбрать: Create sheets
Дополнительную информацию вы можете получить из этого видео:
ВНИМАНИЕ!
У этой статьи есть продолжение: Бесконечные Зависимые Выпадающие Списки в Google Sheets (часть 3)
Бесконечные Зависимые Выпадающие Списки в Google Sheets
В статье рассмотрен вариант создания на листе Google Spreadsheet связанных выпадающего списков, практически не ограниченных ни по числу связанных элементов, ни по количству сток на листе.
Скрипт программы представлен ниже:
function onEdit(e) {
let col = e.range.getColumn();
let list_name = e.source.getActiveSheet().getName();
let name = e.value;
if(list_name=="Home") {
fillColumn(col, name);
}
}
function fillColumn(col, name) {
// find date
var sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var iMax = sd.getLastRow();
var col_data = [];
for(var i=2; i<=iMax; i++) {
var x = sd.getRange(i, col).getValue();
if(x == name) {
col_data.push(sd.getRange(i, col+1).getValue());
}
}
// clear Data_2
var sd_2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data_2");
sd_2.getRange(2, col+1, sd_2.getLastRow(), sd_2.getLastColumn()).clearContent();
// insert data
iMax = col_data.length;
for(var i=2; i<=iMax+1; i++) {
sd_2.getRange(i, col+1).setValue(col_data.shift());
}
}Обратите внимание: имена листов должно ТОЧНО соответствовать именам, указанным в скрипте!
Главный лист: "Home"
лист данных: "Data",лист промежуточных данных: "Data_2"
Эти имена в скрипте выделены красным цветом, поэтому, при желании, их будет не трудно найти, чтобы заменить на имена свои листов.
У этой статьи есть продолжение: Бесконечные Зависимые Выпадающие Списки в Google Sheets (часть 2)
Теперь, в новой версии программы появилась возможность по нажатию всего лишь одной(!) кнопки из пользовательского меню автоматически создавать все листы файла, необходимые для работы скрипта (включая форматирование и валидацию данных).
Генератор документов Google Apps Script с журналом учёта созданных документов
Эта статья является продолжением другой, более ранней статьи Как автоматически вставлять данные в Google Docs, используя Google Apps Script?, в который описывается алгоритм создания копии шаблона документа Google Doc и вставки в него новых данных, полученных из листа Google Spreadsheet.
Здесь мы рассмотрим новую версию программы генерации документов, которая претерпела ряд существенных изменений.
В первую очередь изменения связаны с тем, что в любом мало-мальски приличном делопроизводстве рано или поздно неизбежно встаёт вопрос об учёте созданных документов. Поэтому в новой редакции программы был добавлен журнал регистрации созданных (сгенерированных) документов, где появилось 2 новых поля: номер документа {document_id} и дата документа {document_date}. (В этой версии программы данные новых полей изменяются вручную). Разумеется, при желании этот список может быть существенно дополнен. Главное, не забывать аккуратно копировать уникальные символы новых полей в соответствующие места вставки шаблонов Google Docs.
Журнал регистрации было решено сделать на главном листе ("Main"). Что существенно изменило интерфейс программы. Данные контрагентов переместились на новый лист "Clients":
А поля с чек-боксами для выбора "ОТ КОГО" (FROM) и "КОМУ" (TO) были заменены полями со списком контрагентов, связанным с листом "Clients".
Добавлен также ещё один вариант запуска скрипта - из пользовательского меню (My menu), которое создаётся автоматически при открытии документа.
Перед запуском скрипта теперь необходимо выбрать в новой строке контрагентов FROM и TO, наименование шаблона Google Docs, номер документа и дату документа. И самое главное помнить, что в новом скрипте генерируется тот документ, в строке которого находится активная ячейки.
Полностью обновлённый скрипт программы представлен ниже:
//create user's menu
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu("My menu")
.addItem('Create Document', 'DocGenerator')
.addToUi()
}
//main function
function DocGenerator() {
var sm = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Main");
//find clients info
var row_active = sm.getActiveCell().getRow();
var principal_shart_name = sm.getRange(row_active, 1).getValue();
var attorney_shart_name = sm.getRange(row_active, 2).getValue();
var principal = getClientData(principal_shart_name);
var attorney = getClientData(attorney_shart_name);
//find documents info
var document = {
id:sm.getRange(row_active, 4).getValue(),
date:sm.getRange(row_active, 5).getValue(),
}
// find template
var docName = sm.getRange(row_active, 3).getValue();
var docID = getTemlateID(docName);
var docNameCopy = docName +"_" + document.id +"_" + document.date + "_" + principal.name + "_" + attorney.name;
var file = DriveApp.getFileById(docID);
file.makeCopy(docNameCopy);
var fileCopy = DriveApp.getFilesByName(docNameCopy).next();
var fileCopyID = fileCopy.getId();
// replacement
var body = DocumentApp.openById(fileCopyID).getBody();
body.replaceText('{document_id}', document.id);
body.replaceText('{document_date}', document.date);
body.replaceText('{principal_name}', principal.name);
body.replaceText('{principal_id}', principal.id);
body.replaceText('{principal_id_dateOfIssue}', principal.id_dateOfIssue);
body.replaceText('{attorney_name}', attorney.name);
body.replaceText('{attorney_id}', attorney.id);
body.replaceText('{attorney_id_dateOfIssue}', attorney.id_dateOfIssue);
Browser.msgBox("Completed!");
}
function getClientData(sh_name) {
var sc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Clients");
var iMax = sc.getLastRow();
for(var i=2; i <= iMax; i++) {
if(sc.getRange(i, 1).getValue() == sh_name) {
break;
}
}
var client = {
short_name: sc.getRange(i, 1).getValue(),
name: sc.getRange(i, 2).getValue(),
id: sc.getRange(i, 3).getValue(),
id_dateOfIssue: sc.getRange(i, 4).getValue(),
};
return client;
}
function getTemlateID(docName) {
var st = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Templates');
var iMax = st.getLastRow();
for(var i=2; i <= iMax; i++) {
if(st.getRange(i, 1).getValue() == docName) {
var docId = st.getRange(i, 2).getValue();
return docId;
}
}
}Более подробные объяснения о работе скрипта Вы сможете получить, ознакомившись с данным видео:
Как автоматически вставлять данные в Google Docs, используя Google Apps Script?
Очень часто возникает необходимость создавать однотипные документы, в которых изменяются только данные контрагентов: договора, доверенности, коммерческие предложения и т.д.
Совершенно логичное решение в подобной ситуации:
- создать шаблоны документов Google Docs, куда будут вставляться данные контрагентов;
- создать документ Google Sheets, где будут хранится данные контрагентов и ссылки на шаблоны документов,
- и написать скрипт, который будет управлять процессом выбора контрагентов и вставки данных выбранных контрагентов в шаблоны документов.
Сама идея определения места вставки и последующей вставки на это место нужного значения заключается в создании на листе Google Doc уникального набора символов, который можно было бы однозначно идентифицировать и заменить с помощью метода замены текста replaceText:
body.replaceText("{unique label}", "klient data");
Начнём с таблицы. В нашем примере мы сделали три "информационных столбца": имя клиента, номер ID, дата издания ID. Чтобы облегчить редактирование шаблона имена столбцов мы назовем их также, как и имена меток в Google Docs. И, поскольку это - метки (места вставок), то сразу определим для себя, что метки мы всегда будем заключать в фигурные скобки.
Помимо информационных столбцов здесь также находятся "вспомогательные столбцы", с помощью которых реализован интерфейс нашей программы: КТО выдаёт (кто заключает договор), КОМУ выдаёт (с кем заключает договор) и какой ШАБЛОН документа при этом используется.
В качестве шаблона документа, к которому обращается скрипт, был использован следующий вариант доверенности.
Для того, чтобы различить метки доверителя и поверенного, к меткам доверителя был добавлен элемент текста - [principal_], а к меткам поверенного - [attorney_].
Скрипт, представленный ниже, реализует следующие задачи:
- определение выбранных клиентов и чтение данных этих клиентов с листа Google Sheets;
- нахождение выбранного шаблона в Google Drive, создание копии выбранного шаблона и сохранение её под новым именем;
- открытие документа (только что созданной копии шаблона) и замене меток на данные контрагентов.
function DocGenerator() {
var sc = SpreadsheetApp.getActiveSheet();
//find clients info
var principalIndex = getClientIndx(1);
var attorneyIndex = getClientIndx(2);
var principal = getClientData(principalIndex);
var attorney = getClientData(attorneyIndex);
// find template
var docName = sc.getRange(principalIndex, 3).getValue();
var docID = getTemlateID(docName);
var docNameCopy = docName + "_" + principal.name + "_" + attorney.name;
var file = DriveApp.getFileById(docID);
file.makeCopy(docNameCopy);
var fileCopy = DriveApp.getFilesByName(docNameCopy).next();
var fileCopyID = fileCopy.getId();
// replacement
var body = DocumentApp.openById(fileCopyID).getBody();
body.replaceText('{principal_name}', principal.name);
body.replaceText('{principal_id}', principal.id);
body.replaceText('{principal_id_dateOfIssue}', principal.id_dateOfIssue);
body.replaceText('{attorney_name}', attorney.name);
body.replaceText('{attorney_id}', attorney.id);
body.replaceText('{attorney_id_dateOfIssue}', attorney.id_dateOfIssue);
Browser.msgBox("Completed!");
}
function getClientIndx(col) {
var sc = SpreadsheetApp.getActiveSheet();
var iMax = sc.getLastRow();
var found = false;
for(var i=2; i <= iMax; i++) {
if(sc.getRange(i, col).getValue() == true) {
found = true;
break;
}
}
if(found == true) {
return i;
} else {
Browser.msgBox("Please select at least one client!")
}
}
function getClientData(indx) {
var sc = SpreadsheetApp.getActiveSheet();
var client = {
name: sc.getRange(indx, 4).getValue(),
id: sc.getRange(indx, 5).getValue(),
id_dateOfIssue: sc.getRange(indx, 6).getValue(),
};
return client;
}
function getTemlateID(docName) {
var st = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Templates');
var iMax = st.getLastRow();
for(var i=2; i <= iMax; i++) {
if(st.getRange(i, 1).getValue() == docName) {
var docId = st.getRange(i, 2).getValue();
return docId;
}
}
}Более подробные объяснения о работе скрипта Вы сможете получить, ознакомившись с данным видео:
Продолжнение смотрите в статье:
Генератор документов Google Apps Script с журналом учёта созданных документов.
