Solution title: How to add more questions and still be able to send follow-up emails automatically

Hello,

I have found and appreciated the scripts that help to send follow up emails when a google form is submitted. By following all the steps indicated it works perfectly. The issue comes when I want to do two more adaptations. One changing the topics, second adding more questions. Could you please, guide on how to do so?

Kind regards,

Email Sent: Unexpected token in object literal

hyperlink field: Test Hyperlink

Solution title:
Null error when sending emails without variable tags

One sentence summary:
Programs throws a null error when you try to use a draft without {{}} tags.

Solution:
Add this one-liner to line 145: if(templateVars == null) return JSON.parse(template);

Hi,

I stumbled across https://developers.google.com/gsuite/solutions/mail-merge and would like to share: It works! Kudos to you for putting the together and share it with everyone here.

However, to make it work, I had to delete all other 135 drafts in my draft folder, so that there is only the one remaining which is supposed to be used.

As far as I can tell, the issues is caused by following two lines in script:

// get drafts
const drafts = GmailApp.getDrafts();
// filter the drafts that match subject line
const draft = drafts.filter(subjectFilter_(subject_line))[0];

Having run the script in debug mode, drafts showed up as an array of 136, while draft remained unassigned.

After deleting the other 135 drafts from the draft folder, the script ran through without issues (and without modifications).

Arne

In the Mail Merge solution a known limitation of GmailApp is support for Unicode characters. This means Gmail drafts with emojis like '☺' are replaced with ������.

A quick solution would be to replace GmailApp.sendEmail() with MailApp.sendEmail() here.

Downside of this approach is it's an extra scope.

I am getting the message "Cannot call method "createEvent" of null. with this code". any ideas?

// Copyright 2019 Google LLC
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

var COLUMN_NUMBER = {
EMAIL: 2,
NAME: 3,
START_DATE: 4,
END_DATE: 5,
APPROVAL: 7,
NOTIFIED: 8,
};

var APPROVED_DROPDOWN = {
APPROVED: 'APPROVED',
NOT_APPROVED: 'NOT APPROVED',
IN_PROGRESS: 'IN PROGRESS',
};

var NOTIFIED_DROPDOWN = {
NOTIFIED: 'NOTIFIED',
NOT_NOTIFIED: 'NOT NOTIFIED',
};

var VACATION_REASONS = [
'Vacation',
'Sick leave',
'Maternity/Paternity',
'Bereavement',
'Leave of absence',
'Personal time',
];

var REJECTION_EMAIL_SUBJECT = 'ERR: Vacation Time Request NOT Approved';
var EVENT_TITLE = "VACATION FOR ";

/** TODO: Hard code your manager's email */
var MANAGER_EMAIL = '[email protected]';

/**

• Add custom menu items when opening the sheet.
  */
  function onOpen() {
  var sheetUi = SpreadsheetApp.getUi();
  sheetUi.createMenu('TimeOff')
  .addItem('Form Setup', 'setUpForm')
  .addItem('Column Setup', 'createNewColumns')
  .addItem('Notify Employees', 'notifyEmployees')
  .addToUi();
  }

/**

• Creates a new column for a manager to input her approval
• of each employee's vacation request. Uses a helper function
• to create an additional notification column.
  */
  function createNewColumns() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastCol = sheet.getLastColumn();
  var lastRow = sheet.getLastRow();
  var frozenRows = sheet.getFrozenRows();
  var startRow = frozenRows + 1;
  var numRows = lastRow - frozenRows;

// Creates approval column.
sheet.insertColumnAfter(lastCol);
sheet.getRange(frozenRows, COLUMN_NUMBER.APPROVAL)
.setValue('APPROVAL');

// Sets drop-down menu cells in approval column.
var approvalColumnRange = sheet.getRange(startRow, COLUMN_NUMBER.APPROVAL,
numRows, 1);
var dropdownValues = [APPROVED_DROPDOWN.APPROVED, APPROVED_DROPDOWN.NOT_APPROVED,
APPROVED_DROPDOWN.IN_PROGRESS];
var rule = SpreadsheetApp.newDataValidation().requireValueInList(dropdownValues)
.build();
approvalColumnRange.setDataValidation(rule);
approvalColumnRange.setValue(APPROVED_DROPDOWN.IN_PROGRESS);

// Calls helper function to repeat the above code but for the NOTIFIED column.
createNotifiedColumn();
}

/**

• Adds a column to allow managers to view which employees
• have or have not yet been notified. The value of the cells
• is set to 'NOT NOTIFIED' on default & changed accordingly.
  */
  function createNotifiedColumn() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastCol = sheet.getLastColumn();
  var lastRow = sheet.getLastRow();
  var frozenRows = sheet.getFrozenRows();
  var startRow = frozenRows + 1;
  var numRows = lastRow - frozenRows;

// Sets up column properties.
sheet.insertColumnAfter(lastCol);
sheet.getRange(frozenRows, COLUMN_NUMBER.NOTIFIED)
.setValue('NOTIFIED STATUS');

// Sets column's cells to be drop-down menus.
var notifiedColumnRange = sheet.getRange(startRow, COLUMN_NUMBER.NOTIFIED,
numRows, 1);
var dropdownValues = [NOTIFIED_DROPDOWN.NOTIFIED, NOTIFIED_DROPDOWN.NOT_NOTIFIED];
var rule = SpreadsheetApp.newDataValidation().requireValueInList(dropdownValues)
.build();
notifiedColumnRange.setDataValidation(rule);
notifiedColumnRange.setValue(NOTIFIED_DROPDOWN.NOT_NOTIFIED);
}

/**

• Creates a calendar event for an employee whose vacation

• request has been approved.

• @param {String} employeeName Name of employee.

• @param {String} employeeEmail Email of employee.

• @param {date} startDate Vacation request start date.

• @param {date} endDate Vacation request end date.
  **/
  function createCalEvent(employeeName, employeeEmail,
  startDate, endDate) {
  var managerCal = CalendarApp.getCalendarById(MANAGER_EMAIL);

  // Creates a calendar event.
  var descriptionText = Utilities.formatString('Your vacation time from %s to %s has been approved. Enjoy!',
  startDate, endDate);
  var event = managerCal.createEvent(EVENT_TITLE + employeeName,
  startDate, endDate, {
  description: descriptionText,
  guests: employeeEmail,
  sendInvites: true,
  });
  }

/**

• Sends emails to employees whose vacation time request
• was NOT approved.
• @param {String} employeeEmail Email of employee.
  */
  function sendRejectionEmail(employeeEmail, startDate, endDate) {
  // Craft specific e mail body.
  var emailBody = Utilities.formatString('Your vacation time request from %s to %s has NOT been approved.',
  startDate, endDate);

// Send email.
MailApp.sendEmail(employeeEmail, REJECTION_EMAIL_SUBJECT,
emailBody);
}

/**

• Checks the approval status of each employee and notifies

• them of their status accordingly, either through creating

• a shared calendar event or sending a notification email.

• @param {String} employeeEmail Email of employee.

• @param {String} employeeName Name of employee.

• @param {String} approvalStatus Manager-set status.

• @param {date} startDate Vacation request start date.

• @param {date} endDate Vacation request end date.

• @return {String} Value of whether or not employee needs to be notified.
  */
  function approvalCase(employeeEmail, employeeName,
  approvalStatus, startDate, endDate) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var managerCal = CalendarApp.getCalendarById(MANAGER_EMAIL);

  // Checks approval status.
  if (approvalStatus == APPROVED_DROPDOWN.NOT_APPROVED) {
  // Sends email of disapproval.
  sendRejectionEmail(employeeEmail, startDate, endDate);
  return 'NOTIFY';
  } else if (approvalStatus == APPROVED_DROPDOWN.APPROVED) {
  // Creates calendar event.
  createCalEvent(employeeName, employeeEmail,
  startDate, endDate);
  return 'NOTIFY';
  } else if (approvalStatus == APPROVED_DROPDOWN.IN_PROGRESS) {
  return 'DO NOT NOTIFY';
  }
  }

/**

• Checks the notification status of each employee and, if not notified,
• notifies them of their status accordingly, through use of helper
• functions.
  */
  function notifyEmployees() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  var frozenRows = sheet.getFrozenRows();
  var startRow = frozenRows + 1;
  var numRows = lastRow - startRow;
  var numCols = COLUMN_NUMBER.NOTIFY - COLUMN_NUMBER.EMAIL;

// Go through every employee's information.
for (var i = 0; i < numRows; i++) {
var currentStartRow = i + startRow;

// Obtains current employee's values.
var range = sheet.getRange(currentStartRow, COLUMN_NUMBER.EMAIL,
    1, COLUMN_NUMBER.NOTIFIED - COLUMN_NUMBER.EMAIL + 1);
var rangeValues = range.getValues();

// Ensures does not notify twice.
var notifiedStatus = rangeValues[0][COLUMN_NUMBER.NOTIFIED - COLUMN_NUMBER.EMAIL];
if (notifiedStatus == NOTIFIED_DROPDOWN.NOTIFIED) {
  continue;
}

// Obtains necessary variables for notification.
var employeeEmail = rangeValues[0][COLUMN_NUMBER.EMAIL - COLUMN_NUMBER.EMAIL];
var employeeName = rangeValues[0][COLUMN_NUMBER.NAME - COLUMN_NUMBER.EMAIL];    
var startDate = rangeValues[0][COLUMN_NUMBER.START_DATE - COLUMN_NUMBER.EMAIL];
var endDate = rangeValues[0][COLUMN_NUMBER.END_DATE - COLUMN_NUMBER.EMAIL];
var approvalStatus = rangeValues[0][COLUMN_NUMBER.APPROVAL - COLUMN_NUMBER.EMAIL];

// Calls helper function to check approval & notify accordingly.
var notifyKey = approvalCase(employeeEmail, employeeName, approvalStatus,
              startDate, endDate);

// Set values to 'NOTIFIED'.
if (notifyKey == 'NOTIFY'){
  sheet.getRange(currentStartRow, COLUMN_NUMBER.NOTIFIED)
  .setValue(NOTIFIED_DROPDOWN.NOTIFIED);
}

}
}

/**

• Set up the Vacation Time Requests form, & link the form's trigger to
• send manager an email when a new request is submitted.
  */
  function setUpForm() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  if (sheet.getFormUrl()) {
  var msg = 'Form already exists. Unlink the form and try again.';
  SpreadsheetApp.getUi().alert(msg);
  return;
  }

// Create the form.
var form = FormApp.create('Vacation Time Requests')
.setCollectEmail(true)
.setDestination(FormApp.DestinationType.SPREADSHEET, sheet.getId())
.setLimitOneResponsePerUser(false);
form.addTextItem().setTitle('Employee Name:').setRequired(true);
form.addTextItem().setTitle('Start Date:').setRequired(true);
form.addDateItem().setTitle('End Date:').setRequired(true);
form.addListItem().setTitle('Reason:').setChoiceValues(VACATION_REASONS);

// Set up on form submit trigger.
ScriptApp.newTrigger('onFormSubmit')
.forForm(form)
.onFormSubmit()
.create();
}

/**

• Handle new form submissions to trigger the workflow.
• @param {Object} event Form submit event
  */
  function onFormSubmit(event) {
  var response = getResponsesByName(event.response);
  sendFormSubmitEmail(response);

// Load form responses into a new row.
var row = ['New',
'',
response['Emoloyee Email:'],
response['Employee Name:'],
response['Start Date:'],
response['End Date:'],
response['Reason:']];
var sheet = SpreadsheetApp.getActiveSpreadsheet();
sheet.appendRow(row);
}

/**

• Converts a form response to an object keyed by the item titles. Allows easier
• access to response values.
• @param {FormResponse} response
• @return {Object} Form values keyed by question title
  */
  function getResponsesByName(response) {
  var initialValue = {
  email: response.getRespondentEmail(),
  timestamp: response.getTimestamp(),
  };
  return response.getItemResponses().reduce(function(obj, itemResponse) {
  var key = itemResponse.getItem().getTitle();
  obj[key] = itemResponse.getResponse();
  return obj;
  }, initialValue);
  }

/**

• Sends email notifying the manager a new vacation time request
• has been submitted.
• @param {Object} request Form request details.
  */
  function sendFormSubmitEmail(request) {
  var template = HtmlService.createTemplateFromFile('new-vacationtime-request.html');
  template.request = request;
  template.sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  var msg = template.evaluate();

// Send email to manager.
MailApp.sendEmail({
to: MANAGER_EMAIL,
subject: 'New Vacation Time Request',
htmlBody: msg.getContent(),
});
}

Solution title: Images not sent

One sentence summary: Images not sent

Description (1 - 2 paragraphs): When adding images to the draft email they appear ok, however when sent all that is received is the filename

Design Details:
I have tried dragging and also insert, adding to my photos and then adding but the result is the same

Thanks

Solution title: Generate Sales Report

One sentence summary: Bring sales data from your data warehouse directly into
Google Sheets, and then automatically generate presentations
to summarize wins and opportunities for particular regions
and customers.

Description (1 - 2 paragraphs):

Annual and quarterly reports are a standard part of business
for many enterprises. But automation can help eliminate the
repetition inherent in these periodic obligations.
If you find yourself needing to analyze and present findings
from large sets of stored data, consider using automation
to help streamline your reporting.

This solution creates a tool that connects to sales data in
BigQuery, an analytics data warehouse, from directly within
Google Sheets. A configuration sheet allows a user to provide
parameters for the report, such as the Account Name and
Region. With the click of a button, a customized report
with the latest sales data is automatically created in just
a matter of seconds!

Design Details:
A configuration tab in a Google Sheet lets a user pick an Account Name and Region. Then these values become parameters in a BigQuery query that is connected to another tab via the Data Connector. Once the data is refreshed, an Apps Script creates a Google Slides document based off of a template that includes data for that Account Name and Region.

Once a repo owner has approved this issue, you may use our template directory to make a pull
request for you solution. You may either submit the full implementation to this repo or if you
already have the implementation in another repo, simply link it in the README.

Solution title: Measure volume of topics sent to a your support alias

One sentence summary: Generate analytics from your support alias' inbox
Design Details:
Uses Gmail and Sheets. Give Gmail.getMessages() access to an inbox e.g., [email protected]. Scrape your inbox for basic analytics, e.g., count the number of messages received, unique senders, etc. Output to a google sheet the # of emails you got that week, how many employees you supported, and optionally check for popular topics (e.g., scrape the subject line for key terms(

Once a repo owner has approved this issue, you may use our template directory to make a pull
request for you solution. You may either submit the full implementation to this repo or if you
already have the implementation in another repo, simply link it in the README.

I would like to send out mails with personalised hyperlinks.

When creating the link in the gmail draft message, the {{ and }} characters remain.
When closing the draft and reopening, these characters are replaced with "%7B%7B".

When sending the message, the token is not replaced.
Thank you for your help.
Manu

The code currently uses the filter() function on an array, which will return all drafts that match the draft subject line entered, but always selects the first draft in the filtered array anyway:

const draft = drafts.filter(subjectFilter_(subject_line))[0];

The filter function takes forever if there are any significant number of draft emails because it looks for all messages that match, which requires looking through every draft message the user has. Instead, since we only draft we care about the first match found (as demonstrated by the "[0]" above), I think you can use the find() function instead and speed things up dramatically:

const draft = drafts.find(subjectFilter_(subject_line));

Solution title: Automatically Create Draft Gmail Replies To Form Responses

One sentence summary: Use Apps Script to quickly create draft email replies to Google Form Responses.

Description (1 - 2 paragraphs):
Quickly respond to large volumes of Form Responses by having Apps Script generate draft email replies ready for sending. The draft replies include the form responses, which can be reviewed inside the email. A generic thank you message is created and additional personal feedback can be added before hitting send.

Design Details:
Form submit trigger runs function that creates a draft email in Gmail with the Form Response data included.

I tried the above script but it seems that it didn't work correctly. I ran the script in November 2019, it only updated the events of the team till February 2020 which was set in the script (3 months' advance). Events that were updated in team members' calendars after February 2020 no longer sync. This was strange since in the script the sync frequency was set to be every hour.

Besides the above mentioned, in the team there are total 6 members. Out of 6 members, only 4 members' calendars were shown after the first run of the script. All members' calendars were shared.

Any clue what will be caused for all the mentioned?

Thanks

The first 2 times I tried Mail Merge in Gmail by following the directions here it worked. However, beginning yesterday, it has stopped working. The markers in the email draft correspond to the column headers in the Google Sheet. (I copied the header names and pasted them in the email draft with the double brackets around them, like this: {{First name}}. The emails will send with the email draft to the recipients listed in the Google Sheet, but none of the fields within the email body will fill in. Instead there are blanks where they are missing. This seems like a glitch in the system. What can I do to fix this?

title: Tax Loss Harvest Alert with Google Finance
description: List your stocks and purchase prices in a Google Sheet, and this script will alert you if you can sell the stock for a "tax loss harvest"
labels: Apps Script, Sheets, Google Finance
material_icon: event
create_time: 2020-06-14
update_time: 2020-006-14

Contributed by Jeremy Glassenberg Site

When we purchase a stock and find the value dropped, there's an opportunity to sell that stock, purchase another, and claim a tax deduction. However, many brokerages don't let you know when you have that opportunity.

This simple script works with Google Sheets to alert you for free. Simply copy in your portfolio information, so the sheet knows what stocks you own and at what purchase prices. If anything falls below purchase price, you'll be notified of a tax loss harvest opportunity.

Technology highlights

• The SpreadsheetApp service is used to provide stored information to the Apps Script
• Google Finance formula in Google Sheets are used to check stock prices in real-time and alert the script of changes

Try it

1. Copy this sheet
2. In the calculations tab make sure your information in colums A through D are up to date
3. Copy and apply this [script] script-copy to check for tax-loss harvesting opportunities. [TODO: confirm if this is needed]
4. Set a trigger for the script to run hourly, or daily, or however frequently you want to check for opportunities.

Next steps

To get started with Google Apps Script, try out [the codelab][codelab]
which guides you through the creation of your first script.

You can also view the full source code of this solution on GitHub to
learn more about how it was built.

Hey!

Absolutely love what you have done here https://developers.google.com/gsuite/solutions/content-signup

I am using the brackets on my google doc and everything works (this is my second time using this script) but I have brackets where the input from G form is numbers - They dont show up in the email - just the reference to the brackets

Any ideas?

Solution title:
The starting column of the range is too small
One sentence summary:
Unable to run the Mailmerge

Description (1 - 2 paragraphs):

Design Details:
Please provide a high level design overview with a motivation or problem statement to help us vet your idea.

Once a repo owner has approved this issue, you may use our template directory to make a pull
request for you solution. You may either submit the full implementation to this repo or if you
already have the implementation in another repo, simply link it in the README.

wont send email

Code where values are changed.

var NEW_REQUEST = [
'15.6" high Performance Laptop (Windows)',
'14" high Performance Laptop (Windows)',
'15.6" high performance Laptop (Linux)',
'14" lightweight laptop (Windows)',
'High performance Desktop (Windows)',
'Regular performance Desktop (linux)',
'Wired Mouse',
'Wireless Mouse',
'18" Monitor',
'21" Monitor',
'Laptop Bag',
'UPS',
'Pen drive',

];
var REPAIR_REQUEST = [
'UPS Battery replacement',
'Keyboard replacement',
'Monitor Repair',
'Mouse Replacement',
'RAM Upgrade/Replace',

];
var SUPPORT_REQUEST = [
'Not able to Start PC/Laptop',
'Black Screen when start',
'Blue Screen when start',
'Hang/Screen Stuck',
'Software not working',
'New software Installation',

];

var form = FormApp.create('Equipment Requests')
.setCollectEmail(true)
.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId())
.setLimitOneResponsePerUser(false);
form.addTextItem().setTitle('Employee name').setRequired(true);
form.addTextItem().setTitle('Desk location').setRequired(true);
form.addDateItem().setTitle('Due date').setRequired(true);
form.addListItem().setTitle('NEW_REQUEST').setChoiceValues(NEW_REQUEST);
form.addListItem().setTitle('REPAIR_REQUEST').setChoiceValues(REPAIR_REQUEST);
form.addListItem().setTitle('SUPPORT_REQUEST').setChoiceValues(SUPPORT_REQUEST);

/**

• Handle new form submissions to trigger the workflow.
• @param {Object} event - Form submit event
  */
  function onFormSubmit_(event) {
  var response = mapResponse_(event.response);
  sendNewEquipmentRequestEmail_(response);
  var equipmentDetails = Utilities.formatString('%s\n%s\n%s',
  response['NEW_REQUEST'],
  response['REPAIR_REQUEST'],
  response['SUPPORT_REQUEST']);
  var row = ['New',
  '',
  response['Desk location'],
  response['Employee name'],
  response['Desk location'],
  equipmentDetails,
  response['email']];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Pending requests');
  sheet.appendRow(row);
  }

I was able to fix the code, but the supplied code doesn't replace the #agenda in Google Calendar. Under that section the code listed ##agenda instead. Taking out the extra # made the code work just fine.

Solution title: Easily create and maintain BigQuery tables

One sentence summary: Create BigQuery tables with Google Sheets and Apps Script.

Description (1 - 2 paragraphs):
Creating BigQuery tables using the standard GUI can be a hassle. This app provides an alternative way to create BigQuery tables using the BigQuery Table builder sheet. This Google Spreadsheet automates the creation of the BigQuery tables, documents the tables and supports collaboration around the table schema design.

Design Details:
It is described in this article: https://medium.com/google-cloud/https-medium-com-duizendstra-create-bigquery-tables-with-google-sheets-and-apps-script-a0751b43a952

I thought this might be a GMail problem but am now thinking it must be caused by the script that sends the emails. The embedded image looks fine in the GMail draft. The recipient gets an image placemark with an x in the middle (not found) and the image is there as an attachment. Means I can't have a letterhead on my emails.

I am using Mail Merge to send out Usernames and Passwords created for a project.

If the password contains the " character, it throws a JSON token error.

Should I encapsulate the " character with double " ??

My workaround is to simply replace the " character with some other character, however this is very time consuming as it means that I have to manually go back to the accounts that Google created automatically and change the passwords before sending out.

Your guidance will be appreciated.

Any emojis I have in the email body are translated into question marks inside of diamonds. It seems like the script is messing with the Unicode parsing for the emojis.

When a cell in Sheets has information with a return in it, the email fails to send and the email sent column reports "String contains control character". If I try putting quotes around things it reports that there's an unexpected token.

I'm sending emails to out school club advisors to update their clubs info. One of the fields is a description that can be anywhere from one sentence to several paragraphs. I've also created a field that may or may not have something in it (a request for special information from certain clubs). So that field I'd like to start and end with a return, so that if it exists it will create a paragraph break on either side of it so the email is formatted consistently.

Any idea how I can deal with the carriage returns to get the script to accept them?

I try to create a draft email using your mail merge app and gmail. However, when I send, it removes all line breaks. The output looks like this:

Hi Cameron, This email doesn't have returns. Fen Cameron Fen Director of Research

from:

Hi Cameron,

This email doesn't have returns.  Fen

Cameron Fen
Director of Research

Solution title: Record time and activities with Google Sheets, Calendar and Apps Script

One sentence summary: Record time in the calendar and synchronise the entries to a Google Spreadsheet.

Keeping a record of time spent on tasks can be a challenge. Should you update daily, weekly, make notes, use a spreadsheet or just a piece of paper? The dreaded monthly email, please enter your time, it is the end of the month and we need to send the invoices.

This app enables you to write time in the calendar and synchronise it into Google Sheets for further processing.

Design Details:
Described in this article: https://medium.com/@duizendstra/record-time-and-activities-with-google-sheets-calendar-and-apps-script-41bf69244346

Solution title: Send personalized appreciation certificate to employees

One sentence summary: Automatically customize an existing Google Slide certificate template with employee data in Google Sheets and share them using Gmail.

Description: Using the employee data from a Google Sheet and the 'Employee Certificate' template from Google Slides, we can automate the process of generating fully personalized appreciation certificates and send them as PDF attachments via Gmail in a matter of minutes.

Design Details: Manually creating customized employee certificates in Google Slides can be tiresome. Having to download, attach and send them to each individual can get equally cumbersome. Google Apps Script is the perfect tool to simplify such repetitive work and also eliminate any accidents that end-up having one employee get an appreciation certificate of another.

This setup would make use of the 'Employee Certificate' template from Google Slides and a Google Sheet with all the employee details. The script would start by making a copy of the template and replace some of the key placeholders (like Employee Name, Company name etc.) with data from the sheet. Once a slide for every employee is created, we then run another function that would extract individual slides as a PDF attachment and send it to each employee's email ID.

Solution title: Create a mail merge using Gmail and Google Sheets

One sentence summary: Create and distribute visual rich mail merges from data in Google Sheets

Description (1 - 2 paragraphs):
Simplify the process of producing visually rich mail merges using the Gmail composer and combining it with data from Google Sheets. With this solution you can automatically populate an email template created as a Gmail draft with data from Google Sheets. Merged emails are sent from your Gmail account allowing you to respond to recipient replies.

Design Details:
Google Apps Script is perfect for automating repetitive tasks. Copy/pasting data from Google Sheets into an email can be error-prone, with fields potentially missed or data sent to the wrong recipient. You also want your emails to be visually appealing so that important information can be highlighted. At the same time you don't want to spend hours writing and formatting HTML markup and figuring how to include media like images and attachments.

With this solution you can compose your email as a Gmail draft, using all the build-in features of the compose window to format your text, add images and other media. Once your draft is ready your template can automatically be populated with your data in Google Sheets and emailed to your recipients from your Gmail account. So that you can track emails this solution also records if and when your recipient was emailed.

I was looking into the vacation calendar. Pretty neat! I was wondering why the vacation calendar uses keywords instead of event types? Calendar already has an "out of office" type that could be used automatically.

Solution title: 2 Steps Authentication for all

One sentence summary : Apps script code to automate users 2 Steps enrollment with custom email notifications, manager copying or not, and enforcement with Google Groups.

Description (1 - 2 paragraphs): This script is dedicated to G Suite admins to enforce 2 steps on the domain.
Admin can setup custom email message to notify users with an option to copying manager of users if it is in the directory. This script can replace the Google process, as email is sent by a known person it have more impact and users can contact back support if needed.
As it run with triggers admin just have to add emails in the sheets and the script do the rest. At the end 2 steps is enforced on the account to increase security on the domain.

Design Details:
There is a sheets to add emails with in columns information about the status of each email to know if 2 steps is enforced, if user receive the emails etc....

This script is already a repo : https://github.com/St3ph-fr/2-Step-for-all

Once a repo owner has approved this issue, you may use our template directory to make a pull
request for you solution. You may either submit the full implementation to this repo or if you
already have the implementation in another repo, simply link it in the README.

Hi, may I ask how to make row 8 contains column headings instead of row 1? https://github.com/googleworkspace/solutions/tree/master/mail-merge

When I clear the contents of a row, it still puts any new requests below the cleared rows. How do you delete the information without having to redo the column setup each time?

Any emojis I have in the email body are translated into question marks inside of diamonds. It seems like the script is messing with the Unicode parsing for the emojis.

Solution title: youtube-tracker

One sentence summary: Can't get videos' informations by videos' id

Description (1 - 2 paragraphs): When i run "markVideos" it says: TypeError: Cannot call method "split" of undefined. (satır: 90, dosya: "Code")

How can i solve it ?

Solution title:

One sentence summary: Sign in with Google temporarily disabled for this app

Description (1 - 2 paragraphs):
When I click Mail Merge / Send Emails, and select an account, I get:

Sign in with Google temporarily disabled for this app
This app has not been verified yet by Google in order to use Google Sign In.

Design Details:
Please provide a high level design overview with a motivation or problem statement to help us vet your idea.

Once a repo owner has approved this issue, you may use our template directory to make a pull
request for you solution. You may either submit the full implementation to this repo or if you
already have the implementation in another repo, simply link it in the README.

Solution title:
Attach Google Meet Conferences to Calendar Events

One sentence summary:
Use Apps Script to automatically generate Calendar events with a Google Meet attache.

Description (1 - 2 paragraphs):
My small team relies on automation to make things run. Part of our PD process is a Google Form workflow that kicks off calendar events, document creation, and email notifications. Since we've moved to online learning, we used Apps Script to update our process to automatically add a Google Meet link for any PD that doesn't have to be in person.

Design Details:
Please provide a high level design overview with a motivation or problem statement to help us vet your idea.

We have several scripts which build documents for professional development offered in our school district, starting with a form submission. We recently added an "Online" option to our workshop submission form that will automatically generate a Google Meet link on the calendar event that is created for the workshop. The current flow is the following:

1. A Google Form is completed which gathers event details (date, time, location, description, type).
2. An onFormSubmit trigger in the attached spreadsheet posts a calendar event with the information supplied in the spreadsheet.

• If the submission type is "Online," a Google Meet is attached to the calendar event using the Advanced Calendar service.

3. Templated documents are created in a new Google Drive folder and sent to the facilitator to collect feedback from participants (outside the scope of this solution example).

Solution title: Automatically update a [service] report on a daily basis

One sentence summary: Daily refresh of a report from a third party app

Description (1 - 2 paragraphs):
One barrier to entry for G Suite is that many third party applications provide data either in a web UI or via CSV downloads (then opened in Excel). Because data changes regularly and not everyone has an integration to Sheets, how can you build your own integration so that you have daily data, fresh every morning?

The goal is to pick any third party service with an API, use URLFetch in Apps Script to connect to a report, and then user makes time based triggers to run each night that fetch the data from a report and populate it into a Google sheet.

Design Details:
Please provide a high level design overview with a motivation or problem statement to help us vet your idea.

I am trying to use Sentiment Tools and I have added the API key as directed in line 2. But when I press, save it returns this error
SyntaxError: Unexpected identifier (line 73, file "code.gs")

I scrolled down to line 73, and this is what I see
for each (var entity in nlData.entities) {

Anyone know why this would be the case?

Solution title: Send daily notifications to tell you how many new responses a form has received

One sentence summary: Get a digest view of form activity

Description (1 - 2 paragraphs): using Apps Script and Forms / Sheets, count the total number of new responses received in a given time period and send a formatted email letting the user know how popular their form is.

Design Details:
Please provide a high level design overview with a motivation or problem statement to help us vet your idea.

Once a repo owner has approved this issue, you may use our template directory to make a pull
request for you solution. You may either submit the full implementation to this repo or if you
already have the implementation in another repo, simply link it in the README.

I get an error message when I try to open the script editor.

Hi Martin,

My gmail draft contains basic layout - indentation, line breaks, paragraphs of simple text only. The script sends the emails as one long single line of text. Can you suggest what's happening here?

thanks mate,
Craig.

Last entered request is "ignored" when run the script (doesn't receive any email)

Solution title: Add Schedule Send

One sentence summary: Feature request

Description (1 - 2 paragraphs):
This is a really awesome tool!
The only thing missing would be to be able to schedule a mail merge.
Let me know if this would be possible.

Thanks.

Nice utility, but it seems to leave my email name before the @ rather than the GSuite Account name. Thoughts?

I sent a mail merge from an spreadsheet file with 30 unique rows and 500 emails were sent, some of them without addressee, or attachments. What could be wrong? and how can it be fixed?

Solution title: feature request Email opened or link clicked event

One sentence summary:

Description (1 - 2 paragraphs):
When a user opens an email , clicks on a link update sheet date time.

Design Details:
Please provide a high level design overview with a motivation or problem statement to help us vet your idea.

Lets the sender know if recipients are opening email and interacting with the email, if not can then send again.

Once a repo owner has approved this issue, you may use our template directory to make a pull
request for you solution. You may either submit the full implementation to this repo or if you
already have the implementation in another repo, simply link it in the README.

mail-merge is a cool tool, and will save me a bunch of time. This isn't a huge deal, but would it be possible to represent the formatting in the Google sheet?

For example, I'm working on sending a membership list for a community garden what their fee is, and although I've got "your annual plot fee is {{Fee}}" and the fee is listed in the sheet as $34.30, the email comes through as "your annual plot fee is 34.3".

I could workaround this a bit in the email by doing "your annual plot fee is ${{Fee}}" but I'd still have the issue of dropping the final digit if it's a zero.

I'm hoping there is a better solution.

I tried the script on populating the team vacation calendar. However, it doesn't work well. Out of 6 members in a team, it only updated 4 members' calendars instead of 6.

Also, the calendar isn't syncing as frequently as it was set (example every hour) and the number of months (it was set 3 months in advance) I ran the script in November 2019, it shows the events up to February 2020 anything after it was not updated.

What could be the cause?

Thanks

Solution title: Agenda Maker for Google Calendar

One sentence summary: Creates an agenda for a calendar event you create automatically

Description (1 - 2 paragraphs):

So many meetings are time lost. No agenda means no one is prepared to discuss. No minutes means no one will remember what was decided. Most companies ask everyone to set agendas for their meetings, but can't create a good habit.

It's just too burdensome to create an agenda, but very easy to create a meeting. With Agenda maker, the process of creating an agenda is much easier.

By simply adding "#agenda" to the description of your Google Calendar invite, an agenda will be created for your meeting, with a Google Docs reminder to fill it in.

Design Details:

Technology highlights

• Uses [Apps Script][apps_script], allowing for rapid
  development and worry-free operation.
• Uses the Google Docs API to create a new Google Doc from the Agenda Template
• Uses the Google Drive API to set up an agenda folder
• Uses the Calendar API to detect new events and update the event description with a link to the Agenda.

Try it

First, create the script:

1. Click here to open the code in the Apps Script code editor.
2. Click the menu item File > Make a copy to get your own copy. You can
   later access this copy in the G Suite Developer Hub.

Finally, test that your script is working:

1. Open Google Calendar.
2. Begin creating a new event.
3. In the description, enter a description you'd like, and add "#agenda" where you'd like to create the agenda.
4. Wait for an email from Google Docs, or refresh Google Calendar and click on the event again. You should see a link to a newly created Agenda document to edit.

Note: All attendees will receive the invitation from Google Docs in addition to your invite, to view the agenda. They will have editing rights, but you may adjust the script to give commenter or viewer rights.

If you'd like to adjust the style of the Agenda template:

1. After creating your first agenda in a calendar event, visit to your Google Drive account.
2. In Google Drive there should be a folder named "Agenda Maker - App".
3. Within that folder, look for the file named "Agenda TEMPLATE##". This is the template you can adjust.

Next steps

To learn more about how the bot was made, follow this codelab for
detailed instructions on each step in the process. You can also view the
full source code of this solution on GitHub if you want to jump right
to the end.

(The code is ready in a Github repo, from which I'd be happy to transfer everything here)