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

/**
 * 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);
};

// 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)

  console.log([
   uniqCountries,
   genderCount,
   jobCount,
   ideCount,
   experienceCount,
    programmingLangCount,
  ]);
// Return summary as array
  return [
   uniqCountries,
   genderCount,
   jobCount,
   ideCount,
   experienceCount,
   programmingLangCount,
  ];
};

/**
 *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();
};

/ / 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;
};

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);
};

/**
 *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 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();
};