Automatic Email From Google Sheets

[Skip Gillespie]

I have created a form in Google Forms.  As part of the form, there is a picture taken and that is stored on my Google Drive.  The data populates a Google sheet.  A column has been added to the beginning (column A) of the Form Responses tab which has a checkbox.  A second tab has been created that creates a printable form of the submitted data that has the checkbox selected.

I am trying to write a script that, when a new submission comes in, it automatically clears all the checkboxes, selects the checkbox in the last row where there is data in column B.  The script will then create a .pdf of the form tab and attach it to an email to me.  In addition, the photo that was taken (url in column AX of Form Response Tab) should also be extracted from Google drive and the image file also attached to the email.

Here is what I have:

function clearCheckboxesAndSendEmail() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var formSheet = ss.getSheetByName("Form Responses 1");

  var jhaSheet = ss.getSheetByName("Form Tab");

  // Clear all checkboxes in column A of "Form Responses 1" sheet

  var checkboxRange = formSheet.getRange("A:A");

  var checkboxes = checkboxRange.getValues();

  for (var i = 0; i < checkboxes.length; i++) {

    if (checkboxes[i][0] === true) {

      checkboxRange.getCell(i + 1, 1).uncheck();

    }

  }

  // Find the last row with data in column B

  var lastRowWithData = formSheet.getLastRow();

  while (formSheet.getRange(lastRowWithData, 2).isBlank() && lastRowWithData > 1) {

    lastRowWithData--;

  }

  // Place a check in the checkbox of the last row where column B has data

  formSheet.getRange(lastRowWithData, 1).check();

  // Create PDF of "Form Tab" sheet

  var fileId = ss.getId();

  var blob = DriveApp.getFileById(fileId).getAs('application/pdf').setName('Job_Hazard_Analysis.pdf');

  // Create email

  var emailAddress = "My Email";

  var subject = "Form Report";

  var body = "Please find the attached Form report.";

  // Send email with attached PDF

  MailApp.sendEmail({

    to: emailAddress,

    subject: subject,

    body: body,

    attachments: [blob]

  });

}

So when I run this script, it creates the .pdf out of all tabs of the spreadsheet, not just the Forms tab.  Also, the picture that was taken is not attached.  Instead, it sends (2) pictures from the drive that I have no idea why it chooses them.  It's always the same two images.

Any help would be appreciated.

Skip

[Fabrice Faucheux]

Hi

Certainly! Let's refine your script to ensure it meets all your requirements:

1. Clear all checkboxes in column A of the "Form Responses 1" sheet.
2. Select the checkbox in the last row where there is data in column B.
3. Create a PDF of only the "Form Tab" sheet.
4. Attach the photo from the URL in column AX.
5. Send an email with both the PDF and the photo.

Here's a revised version of your script that should address these requirements:

function clearCheckboxesAndSendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSheet = ss.getSheetByName("Form Responses 1");

  var formTabSheet = ss.getSheetByName("Form Tab");

  // Clear all checkboxes in column A of "Form Responses 1" sheet
  var checkboxRange = formSheet.getRange("A:A");
  var checkboxes = checkboxRange.getValues();
  for (var i = 0; i < checkboxes.length; i++) {
    if (checkboxes[i][0] === true) {
      checkboxRange.getCell(i + 1, 1).uncheck();
    }
  }

  // Find the last row with data in column B
  var lastRowWithData = formSheet.getLastRow();
  while (formSheet.getRange(lastRowWithData, 2).isBlank() && lastRowWithData > 1) {
    lastRowWithData--;
  }

  // Place a check in the checkbox of the last row where column B has data
  formSheet.getRange(lastRowWithData, 1).check();

  // Create PDF of "Form Tab" sheet only
  var formTabId = formTabSheet.getSheetId();
  var url = 'https://docs.google.com/spreadsheets/d/' + ss.getId() + '/export?';
  var exportOptions = 'exportFormat=pdf&format=pdf' +
                      '&size=A4' +
                      '&portrait=true' +
                      '&fitw=true' +
                      '&sheetnames=false&printtitle=false' +
                      '&pagenumbers=false&gridlines=false' +
                      '&fzr=false' +
                      '&gid=' + formTabId;
  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(url + exportOptions, {
    headers: {
      'Authorization': 'Bearer ' + token
    }
  });
  var blob = response.getBlob().setName('Job_Hazard_Analysis.pdf');

  // Get the photo URL from column AX
  var photoUrl = formSheet.getRange(lastRowWithData, 50).getValue();
  var photoId = photoUrl.match(/[-\w]{25,}/);
  var photoFile = DriveApp.getFileById(photoId);
  var photoBlob = photoFile.getBlob();

  // Create email
  var emailAddress = "Your...@example.com";

  var subject = "Form Report";

  var body = "Please find the attached Form report and photo.";

  // Send email with attached PDF and photo

  MailApp.sendEmail({
    to: emailAddress,
    subject: subject,
    body: body,

    attachments: [blob, photoBlob]
  });
}


Explanation of the Script

1. Clearing Checkboxes: The script clears all checkboxes in column A by looping through the values and unchecking those that are true.
2. Finding Last Row with Data: The script finds the last row in column B that contains data.
3. Checking the Last Row: The script checks the checkbox in column A for the last row found.
4. Creating PDF of Specific Sheet: The script uses `UrlFetchApp.fetch` to export only the "Form Tab" sheet as a PDF.
5. Extracting Photo: The script extracts the photo using the URL from column AX.
6. *Sending Email: The script sends an email with the PDF and the extracted photo attached.

Replace `"Your...@example.com"` with your actual email address.

This revised script ensures that only the "Form Tab" sheet is included in the PDF, and the correct photo from Google Drive is attached to the email.