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

Проект, который мне недавно дали на работе, заключался в просмотре электронной таблицы Google, содержащей список отложенных заказов, и отправке электронных писем клиентам, чьи заказы соответствовали определенным критериям. В электронной таблице было около 3000 заказов, и, возможно, около 1500 из этих заказов соответствовали критериям, чтобы им было отправлено электронное письмо. Инструкции заключались в том, чтобы вручную просматривать и отправлять каждое электронное письмо, и, работая в команде из трех человек, я уверен, что мы могли бы удалить эти электронные письма примерно за неделю или около того, но такая задача — отличный вариант использования. для скрипта Google Apps.

Компания описывает программу следующим образом: «Google Apps Script — это скриптовая платформа, разработанная Google для облегченной разработки приложений на платформе Google Workspace». Вместо того, чтобы просматривать и вручную отправлять каждое из этих электронных писем, мы можем написать шаблон электронного письма, а затем написать сценарий и использовать Javascript, который будет проходить по нашей электронной таблице строка за строкой, чтобы отправить шаблонное электронное письмо каждому. желаемый получатель. Существует множество ресурсов, посвященных началу работы со скриптом Google Apps, поэтому я перейду сразу к коду.

Собственно перед написанием любого кода нужны две важные вещи: лист с данными по заказам, которые я собирался разбирать, и шаблон сообщения электронной почты. Я бы порекомендовал сохранить это шаблонное сообщение на отдельной странице электронной таблицы в ячейке A1, но на самом деле это сообщение можно сохранить в любом месте электронной таблицы. В приведенном ниже примере предполагается, что сообщение сохранено в ячейке A1 на отдельной странице электронной таблицы. Ниже я включил шаблон, похожий на тот, который я в итоге использовал. Слова, окруженные знаками процента, — это то, что я буду динамически заполнять в зависимости от каждого заказа (подсказка: я буду использовать функцию замены). Существуют и другие способы написания шаблонов, в том числе написание HTML в реальном скрипте, но для моих целей достаточно возможности сохранения шаблона непосредственно в электронной таблице.

Hello, 

I am writing from ___ regarding your order %ORDERNUMBER% that you placed on
%DATE% for %ITEM%. At this time your order has not been fulfilled. Please
let me know if you still want for the order to be fulfilled, or if you 
would prefer a refund.

Sincerely,
Jacob

Теперь, когда мой шаблон написан и данные готовы к работе, я могу перейти к коду. Чтобы все это сделать, я написал в общей сложности три функции: sendEmail, prepareMessage и sendEmailToList. Первые два, sendEmail и prepareMessage, будут вызываться во время выполнения sendEmailToList. Первый, sendEmail, самый простой.

function sendEmail(to, subject, body){
  MailApp.sendEmail(to, subject,body)
}

Функция MailApp.sendEmail принимает три (или более) аргумента: получатель сообщения, тему сообщения и тело сообщения. Также можно включить четвертый аргумент, озаглавленный параметры, если это необходимо для копирования или скрытой копии кого-либо в электронной почте, а также включить вложения файлов и несколько других различных функций. Меня заинтересовал вариант скрытой копии, так как я подумал, что он может быть полезен при обработке ошибок и казался хорошим способом убедиться, что электронные письма отправляются нужным людям, однако это будет засчитываться в мою ежедневную квоту. лимиты» для отправки писем — об этом позже.

Следующая функция, которая мне понадобилась, называется prepareMessage. Как показано в шаблоне ранее, мне нужно иметь три уникальных значения для каждого электронного письма, поэтому эта функция будет принимать каждое из этих трех значений в качестве аргументов.

function prepareMessage(orderNumber, orderDate, requestedItem){
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
                 .getSheetByName("MessageTemplate")
  const oldSheet = SpreadsheetApp.getActiveSheet()
  SpreadsheetApp.setActiveSheet(sheet)
  const dataRange = sheet.getRange(1,1,1,1)
  const data = dataRange.getValues()
  let message = data[0][0]
  message = message.replace("%ORDERNUMBER%", orderNumber)
  message = message.replace("%DATE%", orderDate)
  message = message.replace("%ITEM%", requestedItem)
  SpreadsheetApp.setActiveSheet(oldSheet)
  return message
}
  

Здесь происходит несколько вещей. Во-первых, я беру конкретный лист, на котором есть шаблон сообщения. В то же время я также сохраняю в переменной «oldSheet» лист, содержащий данные — надеюсь, это станет более понятным с третьей и последней функцией. Когда два листа сохранены в переменных, я устанавливаю лист шаблона сообщения в качестве активного листа. Оттуда в следующих нескольких строках я получаю эту строку сообщения, где, наконец, я получаю фактическое сообщение из электронной таблицы со строкой «let message = data[0][0]». У меня есть ощущение, что может быть лучший способ сделать это — я хотел бы услышать любые предложения. Следующий этап функции, надеюсь, довольно ясен, но именно здесь я нахожу каждый из заполнителей, которые были записаны в шаблон, и заменяю их информацией, специфичной для каждого заказа. Затем важно сбросить активный лист обратно к исходному листу и, конечно же, вернуть сообщение. Я обнаружил, что концепция переключения между активными листами не интуитивно понятна, а также является источником ошибок — будьте с этим особенно осторожны.

Последней и самой большой частью проекта является написание функции, которая будет фактически обрабатывать и просматривать каждую строку/порядок и при необходимости отправлять электронное письмо. Я не упоминал об этом ранее, но я также хочу добавить столбец к каждому соответствующему заказу с какой-либо записью, указывающей, что электронное письмо действительно было отправлено.

function sendEmailToList(){
  //here is where I define what sheet I will be working with
  //in this example the name of the page/sheet that I want is titled DummyData
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DummyData")
  
  //I need to know how many rows are in this sheet in order to 
  //grab the correct number of rows
  const rows = sheet.getLastRow()
  
  //number of relevant columns for my use-case is 30
  //getRange takes start row, start column, end row, end column
  //this will be 0-indexed so subtract 1 from number of rows
  const dataRange = sheet.getRange(2,1,rows-1,30)
  const data = dataRange.getValues()
  //data will be an array containing the information from each row
  //each row will be stored as an array
  //each column value will be indexed as an element of the row array
  for (let i = 0; i < data.length; i++){
    //I need to filter out empty lines as some empty rows are present 
    //in my sheet. I am primarily concerned with order type 
    //which is found at index 0 of the order array
    //I will filter out corporate orders,
    //filter out legal orders.
    //Some orders have already had outreach. If there was outreach then
    //column 28 gets populated, so I 
    //filter out orders for which contact was already made by making sure
    //that column 28 is empty
    if (
        //data[i] represents a single row/order
        //
        data[i][0] &&
        data[i][0] !== "corporate" &&
        data[i][0] !== "legal" &&
        data[i][28] === ''
        )
      {
        //here is where I grab the values that I need for each email
        //data[i][desiredColumnNumber] will get what I need
        const emailAddress = data[i][18]
        const orderNumber = data[i][1]
        const orderDate = data[i][23].toLocaleDateString()
        const requestedItems = data[i][26]
        try {
          sendEmail(
            emailAddress, 
            //I also include the order number in the subject of each email
            `Your Philadelphia School District Records Request - ${orderNumber}`, 
            //I call the prepareMessage function passing in the specific
            //values for reach row as parameters
             prepareMessage(orderNumber, orderDate, requestedItems)
          )
        } catch (e) {
          //If I have any errors when sending the email I want to 
          //log the error message as well as the order number/email
          //of the last processed row. I also want to stop executing
          //this script if any errors do come up
          Logger.log(e)
          Logger.log(emailAddress)
          Logger.log(orderNumber)
          return
        }
        //Here is where I go to the end columns of each row and update the
        //values of the last two columns. i+2 is important. 
        //+1 because i in the for-loop is going through an array that 
        //is 0-indexed, and +1 again since the data that I initially 
        //grabbed actually starts in the second column
        sheet.getRange(i + 2, data[i].length - 1).setValue("No")
        sheet.getRange(i + 2, data[i].length).setValue("Auto emailed student")
    }
  }
}