Статьи

Скрипт для создания документов Google из источников данных электронных таблиц

Ноябрь 12, 2018

Microsoft Office поддерживает «источники данных» для создания, например, писем, счетов, наклеек с адресами и других повторяющихся документов на основе шаблона Microsoft Word и данных Microsoft Excel. Это очень распространенная проблема для малого бизнеса, и у Office было решение этой проблемы с середины 90-х годов. Службы Google, облачная альтернатива Microsoft Office, изначально не предлагают аналогичные функции (или, по крайней мере, если они действительно скрывают это). Тем не менее, вы можете довольно легко создать свой собственный генератор документов, используя скрипты Google Apps, если вы хорошо владеете программированием. В этой записи блога я покажу пример того, как создать такой скрипт, и изучу основы сценариев Google Apps.

1. Составные части генератора документов

У нас есть следующие исходные данные для нашей бизнес-проблемы

  • Электронная таблица Служб Google, содержащая данные клиентов.
  • Шаблон документа Документов Служб Google. Исходя из этого, мы хотим создать документ для каждого клиента, заполнив этот шаблон документа данными из электронной таблицы.
  • Папка Google Drive, где хранятся полученные документы.
  • Скрипт Google Apps, который автоматизирует задачу для нас (на основе Javascript)

В моем случае в сценарии использования генерировались тексты договоров для клиентов на основе данных о цене и качестве обслуживания. Затем я просто экспортировал и отправил полученные документы Google в формате PDF.

Все они хранятся в вашем аккаунте Служб Google на Google Диске. Все редактирование происходит через пользовательский интерфейс Служб Google, внешние инструменты не требуются.

Пример исходных данных (обфусцирован с помощью  obfuscate.js )

Пример шаблона документа (обфусцирован с помощью  obfuscate.js ). Вы можете увидеть исходные ярлыки, незаполненные.

Пример получившегося документа — метки заполнены и больше не выделены жирным шрифтом (обфусцировано с помощью obfuscate.js )

2. Краткое введение в скрипт Google Apps

Сценарии Google Apps могут быть вызваны двумя способами

Поскольку мы не работаем над таблицей, нам нужно использовать первый подход.

Google Apps Script  — это облачный скриптовый язык JavaScript (версия ECMAScript не указана? Работает ли он на V8?), Который предоставляет простые способы автоматизации задач для продуктов Google и сторонних сервисов. Скрипт Google Apps содержит  обширную документацию по API  с примерами и  учебными пособиями , но они все еще могут подвергаться изменениям, поскольку почти все помечено как экспериментальное и уже существует множество устаревших методов. Сценарии Google Apps также могут получать доступ к Google Maps, контактам, электронной почте, сайтам, настройкам домена Google Apps и в основном имеют решение для автоматизации практически всего, что вы можете делать в облаке Google.

Сценарий выполняется на стороне сервера, и у вас есть необычный локализованный пользовательский интерфейс на основе браузера для редактирования и отладки вашего сценария.

Паттерны философии и дизайна пользовательского интерфейса напоминают шаг назад к 90-м годам в среде сценариев Visual Basic. Возможно, разработчики Google Apps хотели этого … чтобы разработчики Visual Basic чувствовали себя как дома. Однако, исходя из веб-разработки, Javascript и общего опыта программирования, вы обнаружите, что отсутствие Firebug / Web Inspector вызывает беспокойство консоли. Это не похоже на любую другую разработку Javascript, хотя, конечно, синтаксис тот же.

Так что мои незначительные жалобы включают, но не ограничиваются

  • Регистрация в приложениях возможна, но трассировка журнала очень нечитаема в пользовательском интерфейсе
  • Программа не имеет конкретной точки входа, вам нужно выбрать функцию с помощью виджета выбора. Это заставляет сценарий чувствовать себя как игрушка.
  • Похоже, что отладчик (и отсутствие консоли) не позволяет вам изменять и динамически высовывать объекты во время выполнения (вызывать функции и т. Д.)
  • Отладчик немного медленный (обход туда и обратно на серверах Google, но все же в значительной степени полезен)
  • Отсутствие низкоуровневых инструментов взаимодействия с пользователем в автономных скриптах (см. Ниже)
  • Документы API и реальность не всегда совпадали (так как все еще экспериментально)

Отладчик в действии

Все могло быть лучше, но в итоге мне удалось выполнить то, что я искал, и я до сих пор не плачу ни копейки за Google Apps, поэтому я счастлив. Кроме того, я не хочу возвращаться в Microsoft Office, если мне не нужно писать хорошо отформатированные печатные документы … Документы Google — это игрушка, которая подходит для создания тяжелых и графически чувствительных документов, таких как предложения … Или презентации … где Keynote — король.

3. Генератор скриптов

В начале скрипта у вас есть константы, которые определяют, с какими данными работать. Вы можете создать пользовательский интерфейс, превращающий скрипт в полноценное веб-приложение, но это слишком громоздкий подход для такой маленькой задачи. Конструктор пользовательского интерфейса казался хорошим, но определенно излишним. Хотя существуют методы API Служб Google Script для выполнения простого вопроса prompt () в браузере, по какой-то причине они не поддерживаются в автономных сценариях… поэтому самым быстрым способом ввода данных в сценарий было простое редактирование самого сценария перед каждым запуском. Я ооочень начал пропускать командную строку … впервые в жизни.

Итак, в начале скрипта вы определяете исходные данные

  • Идентификатор электронной таблицы (вы можете выбрать его из URL при редактировании документа)
  • Идентификатор шаблона документа (вы можете выбрать его из URL при редактировании документа)
  • Идентификатор клиента, который является номером строки электронной таблицы, для текущего запуска скрипта
  • Идентификатор папки драйвера Google, в которой полученный документ будет размещен для обмена. Опять же, вы можете выбрать идентификатор из URL при открытии папки.

Тогда скрипт просто заменяет слова данными. Ключевое слово, подлежащее замене в шаблонном документе, идентифицируется как метки столбца (1-я строка) в данных электронной таблицы. Я почти уверен, что для этого найдутся более эффективные методы, но я не хотел тратить время на то, чтобы пойти по колено к GS, чтобы выяснить его нюансы.

А затем сценарий … пожалуйста, не стесняйтесь изменять в соответствии с вашими потребностями (generator.gs):

/**
 * Generate Google Docs based on a template document and data incoming from a Google Spreadsheet
 *
 * License: MIT
 *
 * Copyright 2013 Mikko Ohtamaa, http://opensourcehacker.com
 */

// Row number from where to fill in the data (starts as 1 = first row)
var CUSTOMER_ID = 1;

// Google Doc id from the document template
// (Get ids from the URL)
var SOURCE_TEMPLATE = "xxx";

// In which spreadsheet we have all the customer data
var CUSTOMER_SPREADSHEET = "yyy";

// In which Google Drive we toss the target documents
var TARGET_FOLDER = "zzz";

/**
 * Return spreadsheet row content as JS array.
 *
 * Note: We assume the row ends when we encounter
 * the first empty cell. This might not be 
 * sometimes the desired behavior.
 *
 * Rows start at 1, not zero based!!!  
 *
 */
function getRowAsArray(sheet, row) {
  var dataRange = sheet.getRange(row, 1, 1, 99);
  var data = dataRange.getValues();
  var columns = [];

  for (i in data) {
    var row = data[i];

    Logger.log("Got row", row);

    for(var l=0; l<99; l++) {
        var col = row[l];
        // First empty column interrupts
        if(!col) {
            break;
        }

        columns.push(col);
    }
  }

  return columns;
}

/**
 * Duplicates a Google Apps doc
 *
 * @return a new document with a given name from the orignal
 */
function createDuplicateDocument(sourceId, name) {
    var source = DocsList.getFileById(sourceId);
    var newFile = source.makeCopy(name);

    var targetFolder = DocsList.getFolderById(TARGET_FOLDER);
    newFile.addToFolder(targetFolder);

    return DocumentApp.openById(newFile.getId());
}

/**
 * Search a paragraph in the document and replaces it with the generated text 
 */
function replaceParagraph(doc, keyword, newText) {
  var ps = doc.getParagraphs();
  for(var i=0; i<ps.length; i++) {
    var p = ps[i];
    var text = p.getText();

    if(text.indexOf(keyword) >= 0) {
      p.setText(newText);
      p.setBold(false);
    }
  } 
}

/**
 * Script entry point
 */
function generateCustomerContract() {

  var data = SpreadsheetApp.openById(CUSTOMER_SPREADSHEET);

  // XXX: Cannot be accessed when run in the script editor?
  // WHYYYYYYYYY? Asking one number, too complex?
  //var CUSTOMER_ID = Browser.inputBox("Enter customer number in the spreadsheet", Browser.Buttons.OK_CANCEL);
  if(!CUSTOMER_ID) {
      return; 
  }

  // Fetch variable names
  // they are column names in the spreadsheet
  var sheet = data.getSheets()[0];
  var columns = getRowAsArray(sheet, 1);

  Logger.log("Processing columns:" + columns);

  var customerData = getRowAsArray(sheet, CUSTOMER_ID);  
  Logger.log("Processing data:" + customerData);

  // Assume first column holds the name of the customer
  var customerName = customerData[0];

  var target = createDuplicateDocument(SOURCE_TEMPLATE, customerName + " agreement");

  Logger.log("Created new document:" + target.getId());

  for(var i=0; i<columns.length; i++) {
      var key = columns[i] + ":"; 
      // We don't replace the whole text, but leave the template text as a label
      var text = customerData[i] || ""; // No Javascript undefined
      var value = key + " " + text;
      replaceParagraph(target, key, value);
  }

}

97
Share: