Вы боитесь отправлять одно и то же письмо группе людей вручную день за днем? Не волнуйтесь, потому что с помощью Google Forms, Google Sheets и небольшой магии программирования с использованием AppScript вы можете автоматизировать эту утомительную задачу и сэкономить драгоценное время. В этой статье я покажу вам процесс автоматической отправки электронных писем с помощью этих инструментов.

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

Предпосылки:

  1. Базовые знания гугл форм
  2. Базовые знания программирования, такие как переменные, индексирование и циклы
  3. Базовый синтаксис Google Sheets и JavaScript (AppScript использует JavaScript!)
  4. Желание учиться!

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

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

Гугл формы

Во-первых, вам нужна форма Google! Вот пример:

Моя форма Google предназначена для отправки сообщения об ошибке, с которой сталкивается пользователь. Они должны ответить на четыре вопроса: имя, адрес электронной почты, сообщение об ошибке и объяснение. После отправки они получат электронное письмо о том, что их ответ принят. Для этого нам нужно перейти в Google Sheets.

Google Таблицы

Как видите, ответов уже несколько, но я сделал это для проверки. В вашем случае должно быть 0.

Нажав «Просмотреть в таблицах», вы будете перенаправлены на лист Google под названием «Ответы формы 1». Я предлагаю переименовать его в «Ответы» для удобства чтения.

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

Вот как выглядит мой лист вместе с моими тестами:

Вы что-то заметили? Я закрыл электронные письма из-за проблем с конфиденциальностью данных. Но это не то, что я хочу, чтобы вы заметили. Я хочу, чтобы вы заметили, что строки обрезаются в строке 7. У вас может быть около 1000 строк. Я предлагаю вырезать ваши строки, потому что это вызовет ошибку в нашей программе. Если ваша таблица Google в настоящее время пуста, ограничьте свои строки только 1 строкой. Кроме того, количество столбцов не соответствует количеству вопросов. У нас четыре вопроса, поэтому столбцов должно быть только четыре! Это не тот случай. Google Sheets автоматически добавляет еще один, который является отметкой времени. Так как насчет других? Я добавил их сам. Идентификатор билета — это не то, на что ответит пользователь, поэтому я должен сгенерировать его сам. Перейдем к столбцу «Число».

Это должен быть номер билета. Формула Google Sheet для этого должна быть:

=ARRAYFORMULA(
IF(A2:A="", "",
TEXT(ROW(A2:A)-1, "000000"
)))

Это то, что автоматически применяет формулу ко всему столбцу. Как видите, благодаря этому все строки имеют номера. Оператор =IF() здесь совсем другой. Кратко обсудим его параметры:

logical_expression --> A2:A=""
value_if_true --> if the logical expression is true
value_if_false --> if the logical expression is false

If the value in column A is "", which is blank, the row on the Number
column should also be blank. Otherwise, a number will be added using
the following: TEXT(ROW(A2:A)-1, "000000")

Еще один столбец, который я добавил, — это столбец «Поиск».

Это как-то связано с листом «Идентификаторы билетов» с использованием формулы =XLOOKUP(). Это формула:

=ARRAYFORMULA(
IF(A2:A="", "",
XLOOKUP(D2:D, 'Ticket IDs'!A2:A5, 'Ticket IDs'!B2:B5)
)
)

Оператор =IF() имеет здесь ту же логику. Для =XLOOKUP() это его параметры:

Мы сосредоточимся только на первых трех: search_key, lookup_range и result_range. Те, что внутри скобок, являются необязательными, поэтому нам не нужно их указывать.

search_key --> D2:D
lookup_range --> 'Ticket IDs'!A2:A5
result_range --> 'Ticket IDs'!B2:B5

The search_key is the one that is needed to find inside the lookup_range.
The result_range will be the result. If the search_key is found on A3 of
the lookup_range, then the result_range will be the value on B3.

AppScript

Теперь, когда Google Forms и Sheets готовы, что дальше? AppScript! Следуйте изображению ниже, чтобы перейти к AppScript:

Проследив за изображением, вы увидите что-то вроде этого:

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

function sendEmail() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses");
  let data = sheet.getDataRange().getValues();
  let lastRow = data.length;
  let name = data[lastRow-1][1];
  let email = data[lastRow-1][2];
  let error = data[lastRow-1][3];
  let explanation = data[lastRow-1][4];
  let id = data[lastRow-1][5];
  let subject = "Error Response: " + id;
  let body = `
    <html>
      <body>
        <h2>Hello ${name},</h2>
        <p>Thank you for submitting your response. Your ticket ID is <strong style="color: blue;">ID: ${id}</strong>. Here is a summary of your submission:</p>
        <ul>
          <li><strong style="color: red;">Error: ${error}</strong></li>
          <li><strong>Explanation:</strong> ${explanation}</li>
        </ul>
        <p>Expect a reply soon from us. Thank you!</p>
      </body>
    </html>
  `;
  if (email != "") {
    GmailApp.sendEmail(email, subject, "", {htmlBody: body});
    console.log("Email sent to " + email + " : "+ name);
  } else {
    console.log("No email found in the last row.");
  }
}

Это код!

let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses");
let data = sheet.getDataRange().getValues();
let lastRow = data.length;

Это объявления переменных. «лист» получает активный лист, который является «Ответами». «данные» — это содержимое активного листа. «lastRow» будет последней строкой активного листа. Причина, по которой я предложил отрезать некоторые строки, заключается в том, что если вы этого не сделаете, «последняя строка» автоматически станет 1000-й.

let name = data[lastRow-1][1];
let email = data[lastRow-1][2];
let error = data[lastRow-1][3];
let explanation = data[lastRow-1][4];
let id = data[lastRow-1][5];

Это объявления переменных для получения значения определенного столбца. Ну, имя переменной уже говорит само за себя!

let subject = "Error Response: " + id;
let body = `
  <html>
    <body>
      <h2>Hello ${name},</h2>
      <p>Thank you for submitting your response. Your ticket ID is <strong style="color: blue;">ID: ${id}</strong>. Here is a summary of your submission:</p>
      <ul>
        <li><strong style="color: red;">Error: ${error}</strong></li>
        <li><strong>Explanation:</strong> ${explanation}</li>
      </ul>
      <p>Expect a reply soon from us. Thank you!</p>
    </body>
  </html>
`;

Теперь это та часть, где вы можете редактировать содержимое электронной почты. Как вы можете видеть в основной части, она должна начинаться и заканчиваться обратным апострофом (`), чтобы иметь несколько строк HTML-кода. Переменные также заключены в ${}, чтобы успешно использовать их внутри HTML-кода.

if (email != "") {
  GmailApp.sendEmail(email, subject, "", {htmlBody: body});
  console.log("Email sent to " + email + " : "+ name);
} else {
  console.log("No email found in the last row.");
}

В этой последней части кода AppScript я хочу, чтобы вы сосредоточились на «GmailApp.sendEmail(email, subject, «», {htmlBody: body});», который отправляет электронное письмо получателю. Блок «если» на самом деле не нужен, но я просто хочу включить его на случай возникновения ошибки.

Все настроено!

Теперь, когда все настроено, давайте попробуем, отправив электронное письмо самому себе.

Отличные новости, это работает! Следуя шагам, описанным в этой статье, вы теперь можете автоматизировать отправку электронных писем с помощью Google Forms и Sheets, экономя ваше время и энергию. Этот метод можно использовать для различных целей, таких как отправка электронных писем с подтверждением, информационных бюллетеней и многого другого. Обладая некоторыми базовыми знаниями о Google Forms, Sheets и AppScript, вы можете легко создавать собственные автоматические электронные письма, адаптированные к вашим конкретным потребностям.