0

I have created a Google Apps script from a Google Sheet. Data is submitted to the sheet via a form.

I had onFormSubmit(e) working fine and sending a custom email to the form respondent. But now it's not working anymore. The trigger is still there to run onFormSubmit when the form is submitted. But now I only get Cannot read property '0' of undefined at onFormSubmit(Code:45:20)

I have no clue what change could have caused this to stop working and I have gone through it several times. Would be very happy for some guidance.

The full script here (adapted from Content-signup at Google Developers):

// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/content-signup

/*
Copyright 2022 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.
*/

// To use your own template doc, update the below variable with the URL of your own Google Doc template.
// Make sure you update the sharing settings so that 'anyone'  or 'anyone in your organization' can view.
const EMAIL_TEMPLATE_DOC_URL = 'https://docs.google.com/document/d/1WTLS6KMAWp8TEN74HVSxk5MFCOqD4AEOQRbx8KWlf0Q/edit?usp=sharing';
// Update this variable to customize the email subject.
const EMAIL_SUBJECT = 'Vertical Challenge - your unique link';



/**
 * Installs a trigger on the spreadsheet for when someone submits a form.
 */
function installTrigger() {
  ScriptApp.newTrigger('onFormSubmit')
      .forSpreadsheet(SpreadsheetApp.getActive())
      .onFormSubmit()
      .create();
}

/**
 * Sends a customized email for every form response.
 * 
 * @param {Object} event - Form submit event
 */
function onFormSubmit(e) {
  
  let responses = e.namedValues;

  // If the question title is a label, it can be accessed as an object field.
  // If it has spaces or other characters, it can be accessed as a dictionary.
  let timestamp = responses.Timestamp[0];
  let email = responses['Email address'][0].trim();
  let firstname = responses['First name'][0].trim();
  let lastname = responses['Last name'][0].trim();
  let name = firstname + " " + lastname;
  const randomUnique = `${Math.random().toString(36).slice(2)}-${Date.now()}`

  let link = "https://docs.google.com/forms/d/e/1FAIpQLSc_NWjVtHwRMTG-dEAXwZEj-ImYKlPvcQKbyuSi3acuAtSpEw/viewform?usp=pp_url&entry.504080580=" + randomUnique + "&entry.713271120=" + firstname;
  


  // If there is at least one topic selected, send an email to the recipient.
  let status = '';
 
    MailApp.sendEmail({
      to: email,
      subject: EMAIL_SUBJECT,
      htmlBody: createEmailBody(name, link),
    });
    status = 'Sent';


  // Append the status on the spreadsheet to the responses' row.
  let sheet = SpreadsheetApp.getActiveSheet();
  let row = sheet.getActiveRange().getRow();
  let column = e.values.length + 1;
  sheet.getRange(row, column).setValue(status);

  Logger.log("status=" + status + "; responses=" + JSON.stringify(responses));
}

/**
 * Creates email body and includes the links based on topic.
 *
 * @param {string} recipient - The recipient's email address.
 * @param {string[]} topics - List of topics to include in the email body.
 * @return {string} - The email body as an HTML string.
 */
function createEmailBody(name, link) {
  
  // Make sure to update the emailTemplateDocId at the top.
  let docId = DocumentApp.openByUrl(EMAIL_TEMPLATE_DOC_URL).getId();
  let emailBody = docToHtml(docId);
  emailBody = emailBody.replace(/{{NAME}}/g, name);
  emailBody = emailBody.replace(/{{LINK}}/g, link);
  return emailBody;
}

/**
 * Downloads a Google Doc as an HTML string.
 * 
 * @param {string} docId - The ID of a Google Doc to fetch content from.
 * @return {string} The Google Doc rendered as an HTML string.
 */
function docToHtml(docId) {

  // Downloads a Google Doc as an HTML string.
  let url = "https://docs.google.com/feeds/download/documents/export/Export?id=" +
            docId + "&exportFormat=html";
  let param = {
    method: "get",
    headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true,
  };
  return UrlFetchApp.fetch(url, param).getContentText();
}
1

1 Answer 1

1

The code expects that the columns in the form responses sheet are named Timestamp, Email address, First name and Last name. Chances are that you have edited one of these names in the spreadsheet. If they appear the same, look for things like leading or trailing spaces.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.