Creating a table using Google Apps Script with an array

function emailArray(){
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Sheet1');
    var startRow = 2;
    var lastRow = sheet.getLastRow();
    var lastColumn = sheet.getLastColumn();
    var range = sheet.getRange(startRow,1,lastRow,lastColumn);
    var data = range.getValues();
    var array = [];
    for(var i=0;i<data.length;++i){
        var row = data[i];
        var rowTemp=[]; 
        for (var j=0; j<row.length; j++)
        {
           if(j==4||j==3||j==1||j==5){continue;} 
            rowTemp.push(row[j])
        }    
        var emailPos = row[3];
        var date = row[2];   
        if(emailPos =='<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="85e0fde4e8f5e9e0b1c5e2e8e4ece9abe6eae8">[email protected]</a>'&& date == 0){
            array.push(rowTemp);
        }
    }
    GmailApp.sendEmail('<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="05607d646875696034456268646c692b666a68">[email protected]</a>','This is a test',array.join('\n\n'));
}

The above script is used to send an email when the date count is 0 and the email address serves as the reference for whom to email. I am trying to format the array in table format but facing difficulties. Are there any solutions on how to achieve this? I have beginner-level expertise in scripting.

After researching various resources, here is another attempt with the following script that did not produce the desired outcome;

   function arrayMail(){
       var ss=SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getSheetByName('Sheet1');
       var startRow = 2;
       var lastRow = sheet.getLastRow();
       var lastColumn = sheet.getLastColumn();
       var range = sheet.getRange(startRow,1,lastRow,lastColumn);
       var data = range.getValues();
       var tableStart = "<table border=\"1\"><tr>";
       var tableEnd = "</tr></table>";
       var array = [];
       for(var i=0;i<data.length;++i){
           var row = data[i];
           var rowTemp=[];
       for (var j=0; j<row.length; j++)
       {
           if(j==4||j==3||j==1||j==5){continue;} 
           rowTemp.push('<td>'+row[j]+'</td>');      
       }    
       var emailPos = row[3];
       var date = row[2];   
           if(emailPos =='<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="0e6b766f637e626b3a4e69636f6762206d6163">[email protected]</a>'&& date == 0){
           array.push(rowTemp);
      }
    }
    var HTMLbody = tableStart +array.join('')+tableEnd;
    GmailApp.sendEmail('<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="91f4e9f0fce1fdf4d1f6fcf0f8fdbff2fefc">[email protected]</a>','This is a test',HTMLbody,{htmlBody:HTMLbody});
    }

The current result does not match the expected format;

Desired output formatting should be like the following example:

Infusion Pump 0
Infusion Pump 0

javascriptarraysgoogle-apps-script

Answer №1

This code snippet provided contains a solution for translating an array into the specified string format mentioned in a Google+ post.

var devices = [
  ['Infusion Pump', 'B', 'C', 'D', 'E', 'F'],
  ['Another Pump', 'B', 'C', 'D', 'E', 'F'],
  ['Defib', 'B', 'C', 'D', 'E', 'F']
];

var formattedString = '';

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

  formattedString = formattedString + '<tr>';

  for (var j = 0; j < devices[i].length; j++) {

    formattedString = formattedString + '<td>' + devices[i][j] + '</td>';

  }

  formattedString = formattedString + '</tr>';
}

console.log(formattedString);

Answer №2

After taking inspiration from SamScholefield's solution, I made some adjustments to the code to fit my specific requirements. Here is the customized final version:

function customizeArrayMail(){
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet1');
var startRow = 2;
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
var range = sheet.getRange(startRow,1,lastRow,lastColumn);
var data = range.getValues();
var array = [];
for(var i=0;i<data.length;++i){
    var row = data[i];
    var rowTemp=[];
    for (var j=0; j<row.length; j++){
         if(j==4){continue;} 
         rowTemp.push(row[j]);         
        }    
    var emailPos = row[3];
    var date = row[2];   
      if(emailPos =='<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="dcb9a4bdb1acb0b9ed9cbbb1bdb5b0f2bfb3b1">[email protected]</a>'&& date == -28){
      array.push(rowTemp);
      }
 }
var str = '';
var bodyEnd = '</table>';
var header = '<table border =1><tr><th>Message</th><th>Date Remind</th><th>Days Left</th><th>Email Address</th><th>Status</th></tr>';
for (var k = 0; k < array.length; k++) {
    str = str + '<tr>';
    for (var l = 0; l < array[k].length; l++) {
    str = str + '<td>' + array[k][l] + '</td>';
        }
    str = str + '</tr>';
var msg = header+str+bodyEnd;
}
GmailApp.sendEmail(Session.getActiveUser().getEmail(),'This is a test',msg,    {htmlBody:msg});
};

View the modified result here

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

Exploring the Functionality of Drag and Drop with JPG and GIF in Internet Explorer 9/10/11

When trying to drag, drop, or select a file using a form in Internet Explorer 9/10/11, only JPEG and PNG files are accepted, while JPG and GIF extensions are rejected. This issue is present in both the code I am using below and in the demo found at https:/ ...

javascriptjqueryinternet-explorerdrag-and-drophtml5-canvas

Send live information to router-link

I'm struggling to pass a dynamic path to vue-router, but I can't seem to get the syntax right. Here's what I've been attempting: <li v-on:click="$emit('closeDropdown')"><router-link :to="item.route" id="button">{{ ...

javascriptvue.jsvue-component

Rearranging components in React does not automatically prompt a re-render of the page

I'm currently working on a project to display the update status of my Heroku apps. The issue I encountered was that the parent component (Herokus) was determining the order, but I wanted them sorted based on their update dates starting from the most r ...

javascriptreactjsherokuecmascript-6

Before being sent, CDATA is eliminated

Currently, I am integrating a SOAP call within an Angular application. One requirement I have is to include CDATA for a specific section of the payload for certain calls. angular.forEach(contactsCollection, function (item, index) { contacts = contact ...

javascriptajaxangularjssoap

triggering e.stopImmediatePropagation() within an onclick event handler

Is there a way to retrieve the event object from an onclick attribute? I attempted the following: <a href="something.html" onclick="function(e){e.stopImmediatePropagation();}">Click me</a> In addition, I tried this: <a href="something.ht ...

javascriptjqueryhtmleventsonclick

Location of the webpage determined by the specific value

I need help with a specific request. I am looking to make some modifications to this form by adding two fields - one for "male" and one for "female" - each assigned to their respective URLs. For example, selecting the "female" option should redirect to " ...

javascripthtml

Verify if an express module has a next() function available

Is there a method to check if there is a function after the current middleware? router.get('/', function(req, res, next){ if(next){//always returns true } }); I have a function that retrieves information and depending on the route, thi ...

javascriptnode.jsexpressmiddleware

Issues with Braintree webhooks and CSRF protection causing malfunction

I have successfully set up recurring payments with Braintree and everything is functioning properly. Below is an example of my code: app.post("/create_customer", function (req, res) { var customerRequest = { firstName: req.body.first_name, lastN ...

javascriptnode.jsexpresscsrfbraintree

What is the most effective method to return a promise within an object using JavaScript?

Within this section of my code, I am attempting to determine whether a given number is a CPF or CNPJ and provide a validation result. However, the Validate() function only returns {valid : Promise {}}, rather than the expected {valid: true} object. I have ...

javascriptnode.jsjsonobjectbrowser

SSL handshake failure in Firefox when making cross-domain requests with client certificates over xhr

The scenario is as follows: Using Firefox (versions 3.x and 4b) with functioning certificates, including a client certificate. Accessing a web page with an AJAX call using the XMLHttpRequest() method to a different subdomain. A custom web server located ...

javascriptajaxfirefoxhttpsclient-certificates

grab the content from a text editor and insert it into a div element

Currently, I am trying to extract text from my content editable div and place it in another div (similar to the one seen on stack overflow). However, I am encountering a frustrating issue. 1. My content div seems to be lagging behind. When I type in my ed ...

javascripthtmlcss

Changing the structure of a webpage in real-time and inserting new elements into the document

I have a custom x-template filled with a survey element (including a text field and radio button). Upon loading the screen, the database sends a JSON object to the UI based on previously stored sections. This JSON object is then used to populate the survey ...

javascriptjqueryjson

Indication of a blank tab being displayed

I'm struggling to get my Angular directives working in my project. I've tried implementing a basic one, but for some reason it's not showing anything on the screen. Can anyone help me troubleshoot this issue? Here is my JS code: angular ...

javascriptangularjsangularjs-directive

Issue with the bootstrap toggle menu script not functioning as expected

My toggler menu is not working and I'm not sure why. I'd also like to know which links to remove and how to improve my website's speed. When I resize the browser window, the toggle menu icon is not displayed correctly and does not open a new ...

javascripthtmlcssbootstrap-5

Error occurs when passing a service as a parameter to a controller in AngularJS

After adding 'loginService' as a parameter to the controller in order to reference the service I want to use, I encountered an error that caused the rest of my angular functions to stop working. app.controller('loginController', functi ...

javascriptangularjssingle-page-applicationlogin-scriptangular-services

What is the best way to upgrade to a specific version of a child dependency within a module?

npm version: 7.24.2 Looking for assistance on updating a child dependency. The dependency in question is: vue-tel-input This dependency relies on libphonenumber-js with version ^1.9.6 I am aiming to update libphonenumber-js to version ^1.10.12. I have ...

javascriptvue.jsnpmversionvue-tel-input

What steps should I follow to generate a table using Ajax and Javascript when a button is clicked?

After spending hours following various tutorials and exploring previously asked questions, I am struggling to make this work. My HTML page looks like this: <!DOCTYPE html> <html> <head> <link type="text/css" rel="styleshee ...

javascriptphpjqueryhtmlajax

Tips on inputting information into a json file once you have completed crawling with casperjs?

I have written code that extracts a large number of words (innerHTML) from various webpages. and I am interested in directly inserting this data into a json file.. Below is the JavaScript code snippet... var words = []; var casper = require('casper ...

javascriptruby-on-railsjsoncasperjs

Determining whether a string includes any elements from an array

Recently, I've embarked on a journey to learn javascript and ventured into developing a small chrome extension. This extension aims to scan item listings on a specific website for products that match keywords provided by users. I am seeking guidance o ...

javascriptarraysstring

Endless jasmine timeout

This question is a continuation of a discussion on the Remove timeout for single jasmine spec GitHub issue. Query: Can a single test be configured to never time out? The Issue: While it's possible to set a global timeout value using DEFAULT_TIMEOU ...

javascripttestingjasmineprotractor