Sending emails based on cell values using Google Apps Script

I'm exploring Google App Scripts and working on a spreadsheet that has three columns: name, overdue, and last contacted. You can view the spreadsheet here.

My goal is to send myself an email reminder containing the names of individuals marked as overdue in column B.

Currently, I've managed to create a script that sends an email for row 2 only. I'm struggling to figure out how to loop through the values in column B.

Below is my script:

function sendEmail() {
  
  var overdueRange = SpreadsheetApp.getActiveSpreadsheet().getRange("B2"); 
  var overdueValue = overdueRange.getValue();
  if (overdueValue === "Overdue") {
    var nameRange = SpreadsheetApp.getActiveSpreadsheet().getRange("A2");
    var name = nameRange.getValues();
    var message = 'Reach out to ' + name;
    var subject = 'Reach out to this person.';
    MailApp.sendEmail('<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ee83978b838f8782ae8b838f8782c08d8183">[email protected]</a>', subject, message);
  }
}

sendEmail()
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-apps

Answer №1

Insight:

To clarify the process, consider the following key points:

  • If you are running sendEmail() from the script editor, ensure that you only call it once without using the 'global' function declaration. This prevents inadvertently calling sendEmail() twice.

  • Avoid using getRange directly on a general spreadsheet object. Instead, specify a sheet by its name or position as shown in the sample script below (using Sheet1). Adjust this to match your own sheet's name.

  • Note that the variable name in your code represents a 2D array, not a single value. Ensure correct handling of this data structure.

  • The provided script defines an array with columns A and B, where names are in column A and overdue status in column B. Iterating through this array allows retrieval of individual values for processing. The script uses forEach method, but you can choose your preferred iteration technique.

Resolution:

function sendEmail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1'); // Update with your sheet's name
  const data = sh.getRange('A2:B'+sh.getLastRow()).getValues();
  data.forEach(r=>{
     let overdueValue = r[1];  
     if (overdueValue === "Overdue"){
         let name = r[0];
         let message = 'Reach out to ' + name;
         let subject = 'Reach out to this person.'
         MailApp.sendEmail('<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="eb86928e868a8287ab8e868a8287c5888486">[email protected]</a>', subject, message); 
     }
  });  
}

Compatible Sheet Structure:

https://i.sstatic.net/98p4u.png

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Handling exceptions in XMLHttpRequest.open() method

Here is a snippet of code I am working with: xhttp=new XMLHttpRequest(); xhttp.open("GET",doc_name,false); xhttp.send(); xmlDoc=xhttp.responseXML; if(xmlDoc==null) { xmlDoc=loadXMLDoc(defaultXml); } While this code functions properly by loading a defa ...

javascriptajaxexceptionxmlhttprequest

Transferring Python JSON object containing Japanese characters to JavaScript

My Python3 function generates a JSON object with the following structure: {'tags': {'releaseArtist': ['Shuichi Murakami', 'Nobu Caine', 'Electro Keyboard Orchestra', 'Mash', 'Loser', &a ...

javascriptpythonjsonutf-8

A guide to dynamically extracting values from JSON objects using JavaScript

I have a JSON array with a key that changes dynamically (room number varies each time I run the code). My goal is to access the inner JSON array using this dynamic key. Here's what I've attempted so far, but it's throwing an error. Here is ...

javascriptarraysjson

Error encountered during automatic form submission

My current project involves creating a web notepad that automatically saves changes every minute. The notes are stored in a field called "notas" for each user in the database. Here is some of the code I am using: <html> <head> < ...

javascriptphpmysqlforms

Having trouble getting the jQuery AJAX post syntax to work properly

I am trying to send a post json value to another page using AJAX. However, when I include the "json" parameter in my POST request, the JavaScript code stops working. If I remove the "json" parameter, it works fine. I am still learning jQuery so any help ...

javascriptjqueryajaxjson

The best way to consistently execute setTimeout in order, regardless of the timing, is by utilizing a Promise

I have the following code snippet: var timeOne = new Promise(function(resolve,reject) { setTimeout(function(){ console.log('This is one'); }, Math.random() * 1000); }); var timeTwo = new Promise(function(resolve,reject) { s ...

javascript

I am struggling with grasping the concept of the event system

I'm encountering an issue with the following code snippet: <template> <div class="chart" v-bind:style="chartStyleObject" v-on:mousedown.left="initHandleMousedown($event)" v-on:mouseup.left="initHandleMouseu ...

javascriptnode.jsvue.jsnuxt.js

Guide on retrieving POST data in sveltekit

Hello, I'm new to sveltekit and I am trying to fetch post data in sveltekit using a POST request. Currently, I am utilizing axios to send the post data. const request = await axios .post("/api/user", { username, email, ...

javascriptnext.jssveltesveltekit

Exploring Bootstrap datatables to search through nested table data with Codeigniter

I have implemented a table using bootstrap datatables and successfully enabled search functionality within the table. However, I have also included nested tables within each row of the main table. These nested tables are supposed to be displayed when clic ...

javascriptjqueryhtmltwitter-bootstrapcodeigniter

How can I prevent my Vue.js application from losing focus when navigating to different components?

I am encountering an issue with my Vue.js app where the focus is lost when changing routes. Initially, when the site loads, the first element of the header component is correctly in focus as desired. However, when navigating to a different link on the site ...

javascriptvue.jsvuejs2vue-componentvue-router

Is there a way to connect a Button to a different page in VUE.JS?

I currently have a button that needs to be linked to another page. This is the code I am working with at the moment. How can we write this login functionality in vue.js? It should direct users to the page "/shop/customer/login" <div class="space-x ...

javascriptvue.js

Issue with Vuetify v-alert not appearing after updating reactive property

I am trying to set up a conditional rendering for a v-alert if the login response code is 401 Unauthorized. Here is how I have defined the alert: <v-alert v-if="this.show" type="error">Invalid email and/or password.</v-alert> Within the data ...

javascripthtmlvue.jsvuejs2vuetify.js

How can I create a popup window that meets standards using XHTML and Javascript?

As I adhere to writing standards compliant XHTML Strict 1.0, I refrain from using the HTML "target" attribute on anchor elements. I have come across 2 distinct methods involving Javascript: One approach involves locating all links with rel='exter ...

javascripthtmlxhtmldom-eventsstandards

Find the element that is being scrolled in order to delete its attributes

Issue with the sidebar causing whitespace on mobile devices, and scroll properties need to be removed. When expanding the sidebar, white space appears below it. Various display modes have been tried, but they all push elements below instead of keeping th ...

javascripthtmljquerycssangular

An error occurs in the console stating 'Maximum call stack size exceeded' when trying to add data to the database using JavaScript

I've been struggling with a JavaScript error for the past few days and despite scouring through numerous answers on StackOverFlow, none seem to address my specific issue. My goal is to simply submit a record to the database using a combination of Jav ...

javascripthtmljquery

Blurred entities aligning in front of one another (THREE.JS)

While experimenting with three.js, I encountered an issue with objects appearing fuzzy when they overlap. The distortion looks like this. Can anyone provide assistance with this problem? Below is the code I'm currently using: // Initializing basic o ...

javascriptthree.js

Route creation unsuccessful while attempting to both download a file and send a response message, resulting in the error "Headers cannot be set after they have been sent to the client."

I'm currently working on setting up a route that will allow users to download a file and receive a response message in the HTML. I attempted to use the download function from express JS, but encountered some issues. Here is the code I am using: route ...

javascriptnode.jsexpressroutes

What steps should I take to update the image src within my slider?

I have a slider image that I want to fade to the next image when a button is clicked. I used JQuery to create a smooth transition when changing the image source, but the image remains the same after fading. Html <button onclick = "prev()" id = "pr ...

javascriptjqueryhtmlcssimage

Ways to showcase an array with HTML content in AngularJS without using ng-repeat

Can someone help with this coding issue? "elements" : ["<p>Element 1</p>","<span>Element 2</span>"] I want to achieve the following output: <div id="wrapper"> <p>Element 1</p> <span>Element 2</s ...

javascriptangularjsangularjs-ng-repeatng-bind-html

Utilize AngularFire to generate a new User and invoke the factory function to showcase notifications

I recently started working with AngularJS and wanted to integrate it with AngularFire/Firebase for storing project data. I created a factory that manages notifications to be displayed in a centralized location. Here is the structure of my factory: myApp.f ...

javascriptangularjsfirebaseangularfireangular-promise