Как автоматически отправлять ответы Google Forms по электронной почте?

Как автоматически отправлять ответы Google Forms по электронной почте?

Скрипт Google Apps для автоматизации

вступление

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

Привет, ребята, это Нибеш из Khadka Coding Lounge. Сегодня я представляю вам новый захватывающий блог о скриптах приложений Google.

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

Предварительное требование

Для этого вам понадобится учетная запись Google и диск Google, который, я думаю, есть у всех. Во-вторых, вам необходимо знание JavaScript. Все остальное я объясню вкратце.

Создать форму Google

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

Таблица Google

Откройте электронную таблицу и создайте еще один лист. Давайте назовем один по умолчанию Исходные ответы, а новый — Обработанные ответы. Мы будем использовать один для сбора всех ответов, а другой — для уточнения ответов только для тех, которые мы вышлю участникам опроса.

Теперь перейдите в раздел Расширения›Скрипты приложений и откройте редактор скриптов. Всего мы создадим три файла. Я назову их create_menu, create_send_content и препроцессоры.

Напоминание: не указывайте расширения для файлов, таких как fileName.gs. Об этом позаботится редактор.

Обработанный лист ответов

Прежде чем мы продолжим, перейдите к листу обработанных ответов и добавьте шесть названий столбцов в первую строку: Страна, Пол, Должность, IDE, Опыт и Языки программирования. Мы будем анализировать только эти столбцы.

preprocessors.gs

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

/**
 * This app script fetches survey responses
 *Filters them and saves them to another file 
* On the second file, it aggregates the columns and returns a nice summary
*/
let fillSecondSheet = () => {
  // Get the spreadsheet 
  let ss = SpreadsheetApp.getActiveSpreadsheet();
// Get the original response sheet
  let surveyResponseSheet = ss.getSheetByName("Original Responses");
// Get process response sheet
  let processedResponseSheet = ss.getSheetByName("Processed Responses");
  // Get the Last row for indexing 
  let lastRow = surveyResponseSheet.getLastRow();

  let values = surveyResponseSheet.getRange(2, 4, lastRow - 1, 6).getValues();
  // console.log(values);
// Set values for response sheet
  processedResponseSheet.getRange(2, 1, values.length, 6).setValues(values);
};

Теперь мы создадим еще одну функцию, которая проанализирует второй лист, а затем вернет общее количество участников по странам, языкам программирования, IDE и т. д.

// Function takes arrays counts values and returns as dictionaries
let countUnique = (arr) => {
  return arr.reduce((initObj, currVal) => {
    initObj[currVal] =
      initObj[currVal] === undefined ? 1 : (initObj[currVal] += 1);
    return initObj;
  }, {});
};
let analyzeSecondSheetData = () => {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let processedResponseSheet = ss.getSheetByName("Processed Responses");
  let lastRow = processedResponseSheet.getLastRow();
// Get the country column and use countUnique() function to get unique countries
  let countryCol = processedResponseSheet
    .getRange(2, 1, lastRow - 1, 1)
    .getValues()
    .flat();
  let uniqCountries = countUnique(countryCol);
  let genderCol = processedResponseSheet
    .getRange(2, 2, lastRow - 1, 1)
    .getValues()
    .flat();
  let genderCount = countUnique(genderCol);
  let jobCol = processedResponseSheet
    .getRange(2, 3, lastRow - 1, 1)
    .getValues()
    .flat();
  let jobCount = countUnique(jobCol);
  let ideCol = processedResponseSheet
    .getRange(2, 4, lastRow - 1, 1)
    .getValues()
    .flat();
  let ideCount = countUnique(ideCol);
  let experienceCol = processedResponseSheet
    .getRange(2, 5, lastRow - 1, 1)
    .getValues()
    .flat();
  let experienceCount = countUnique(experienceCol);
  // Need to do some coding to extract all the programming languages as unique
// Since the values are saved as a string separated with a comma: for instance, Python, Swift, Rust
// We want arrays 
  let programmingLangColInit = processedResponseSheet
    .getRange(2, 6, lastRow - 1, 1)
    .getValues()
    .flat()
    .map((item) => (item.indexOf(",") == -1 ? item : item.split(",")))
    .flat();
  // Formatting the string, trim extra space, uppercase first and lowercase the rest of the letters
  programmingLangCol = programmingLangColInit.map(
    (item) =>
      item.trim().charAt(0).toUpperCase() + item.trim().slice(1).toLowerCase()
  );
  let programmingLangCount = countUnique(programmingLangCol);
  //console.log(programmingLangCount)
  // Return summary as array
  return [
   uniqCountries,
   genderCount,
   jobCount,
   ideCount,
   experienceCount,
   programmingLangCount,
  ];
};

Создать меню в электронной таблице

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

create_menu.gs

/**
 *This file is for creating a Menu on the spreadsheet.
 **/
let onOpen = (e) => {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu("Helper Menu")
    .addItem("Fill Second Sheet", "fillSecondSheet")
    .addToUi();
};

Кстати, onOpen — это не произвольное имя, это зарезервированное ключевое слово function для создания пользовательского интерфейса меню.

Создайте контент для отправки

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

create_send_content.gs

/ / Function that'll loop through dictionary
// return list items containing keys and values 
let dictTolistItems = (arr) => {
  let listStr = "";
  for (const [key, val] of Object.entries(arr)) {
    listStr += `<li> ${key}: ${val}</li>`;
  }
  return listStr;
};
// create content 
let createContent = () => {
  // De-structure the values 
  let [
    uniqCountries,
    genderCount,
    jobCount,
    ideCount,
    experienceCount,
    programmingLangCount,
  ] = analyzeSecondSheetData();
  let countries = dictTolistItems(uniqCountries);
  let gender = dictTolistItems(genderCount);
  let job = dictTolistItems(jobCount);
  let ide = dictTolistItems(ideCount);
  let experience = dictTolistItems(experienceCount);
  let programming = dictTolistItems(programmingLangCount);
  // const fileName = "Survey Report"
  let content = `
  <br>
  <strong>Participants Info: </strong><br>
<br>
  <p>
  <strong>Number of Participants By Countries </strong>: <ul> ${countries} </ul> 
  </p>
  <p>
  <strong>Gender Of Participants</strong>:  <ul> ${gender} </ul> 
  </p>
  <p>
  <strong>Job Roles Of Participants</strong>:  <ul> ${job} </ul> 
  </p>
  <p>
  <strong>Number of Preferred IDEs </strong>: <ul> ${ide} </ul> 
  </p>
  <p>
  <strong>Years of Experiences</strong>:  <ul> ${experience} </ul> 
  </p>
  <p>
  <strong>Programming Languages Used</strong>:  <ul> ${programming} </ul> 
  </p>
  `;
  return content;
};

Отправить электронное письмо участнику опроса

Прежде чем мы напишем функцию для отправки электронных писем участникам, давайте создадим еще один столбец в конце «Исходного листа ответов» с именем Ответили в. В качестве значения будет указана либо дата, когда был отправлен ответ, либо пустая ячейка, если ответ не был отправлен. отправил.

После этого добавьте функцию sendEmail().

create_send_content.gs

let sendEmail = () => {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let s1 = ss.getSheetByName("Original Responses");
  let lastRow = s1.getLastRow();
  let lastColumn = s1.getLastColumn();
  // Get data range from second to last row and second column to the last one
  let dataRange = s1.getRange(2, 2, lastRow - 1, lastColumn - 1).getValues();
  const subject = "Survey Stats";
// Loop over each row to check if the email is replied 
// if not send an email 
// then update replied column
  dataRange.forEach((data) => {
    let recipentName = data[1];
    let content = createContent();
    let email = data[0];
    let body = `Dear ${recipentName},
    <br><br>
    <p> 
    We would like to thank you for your participation in the survey.
  <br>
    We've sent you participation results up until now as follows:
  <br><br>
    ${content}
  <br><br>
     Sincerely, 
     <br>
     Code Eaters
     </p>
    `;
    if (data[data.length - 1] === "") {
      // If the email has not been sent
      MailApp.sendEmail({ to: email, subject: subject, htmlBody: body });
      // Create date values to fill in after the mail is replied in sheet
      let newDate = new Date();
      let datetime =
        newDate.getDate() +
        "-" +
        newDate.getMonth() +
        "-" +
        newDate.getFullYear() +
        "," +
        newDate.toTimeString().slice(0, 8);
      data[data.length - 1] = datetime;
    }
  });
  s1.getRange(2, 2, lastRow - 1, lastColumn - 1).setValues(dataRange);
};

Давайте обновим функцию onOpen и добавим функцию sendEmail в наше меню электронной таблицы.

create_menu.gs

/**
 *This file is for creating a Menu on a spreadsheet.
 *
 **/
let onOpen = (e) => {
  let ui = SpreadsheetApp.getUi();
  ui.createMenu("External Helper Menu")
    .addItem("Fill Second Sheet", "fillSecondSheet")
    .addItem("Send Email", "sendEmail")// New line
    .addToUi();
};

Установить триггеры

Напишем функцию как триггер, который система будет искать после отправки формы.

create_send_content.gs

// Create a function to use as a trigger every time a form is submitted
let comboFunction = () => {
  // First Fill the second sheet
  fillSecondSheet();
  // Analyze the second sheet to send to the user
  analyzeSecondSheetData();
  // Then send the result of the analysis to the user
  sendEmail();
};

После этого нам нужно добавить эту функцию в триггер.

1. На левой панели выберите триггер (тот, что со значком часов).
2. Нажмите кнопку «Добавить триггер».
3. Выберите comboFunction в качестве функции для запуска.
4. Выберите From SpreadSheet в качестве источника событий.
5. Выберите При отправке формы в качестве типа события.
6. Выберите уведомления об ошибках по своему усмотрению.

Затем нажмите «Сохранить», как показано на изображении ниже.

Краткое содержание

Давайте вспомним, что мы делали в этом уроке.

1. Мы создали гугл форму
2. Написал код для уточнения исходных ответов.
3. Обобщил ответы.
4. Создана автоматизированная система, которая отправляет электронные письма участникам, у которых есть последние сводные результаты опроса.

Спасибо

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

Я делаю веб-сайты WordPress, мобильные приложения Flutter, анализ данных с помощью Python, надстройки Google, создаю контент и многое другое. Если вам нужны мои услуги, дайте мне знать.

Спасибо за ваше время.