Google Sheets Auto Email Script

Question

good day to all

I have script here, fyi I don't know much about writing functions. I've managed to frankenstein some scripts I found to get it working, sort of. It works with one function doing one sheet but as you will see i need it to do all 3 sheets and i get a too many triggers error when i duplicate the function.

I have 4 sheets in this doc, a control (Master), then 3 pulling data from that (AZ,CA,NH).

I then have a formula that pulls random lines from that data. on each of the 3 pages (AZ,CA,NH), I'm generating random inventory part cycle counts list.

I got the email working, then i did a workaround by adding a =today() and if statement to my formula to get it to recalculate on edits. and got a script to edit the workbook (replace a cell (G1) contents). works great.

Here is the problem. Can I condense this into one function? everything is the same across the pages (AZ,CA,NH), same # of rows and columns and same placement of data. Here is my code.

*Looks like I ran out of space on the code or something. I basically duplicated this 2 more times and changed the sheet name to "CA" & "NH" the function and triggers to email_ca() & email_nh() as well as myRecalculate()

//AZ Script

function email() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var bulk = ss.getSheetByName("AZ");
  var lastrow = bulk.getLastRow();

  var data1 = bulk.getRange(6, 7, lastrow).getValues(); // part number column

  var report = ""
  var email ="";
  for(var i in data1){
    if(data1[i]!="") email += "<tr><td style='padding:5px'>" + data1[i];
  }

  report ="<table><tr><th>Part Number</th></tr>" + email + "</table>";

  MailApp.sendEmail(Session.getActiveUser().getEmail(),
                "Cycle Count", report, {htmlBody: report});
}

 ScriptApp.newTrigger("email")
   .timeBased()
   .onWeekDay(ScriptApp.WeekDay.THURSDAY)
   .create();

function myRecalculate() { 

// The code below opens a spreadsheet using its ID 
// Note that the spreadsheet is NOT physically opened on the client side. 
// It is opened on the server only (for modification by the script). 
var ss = SpreadsheetApp.openById("13SAt0XQgHQz1JRhXyvwbL6ASfnlLjvFXJQsHfYRM1nE"); 
var sheet = ss.getSheetByName("AZ"); 
var cell = sheet.getRange('G1') 

cell.setValue("Randomizer!!!") 
}

 ScriptApp.newTrigger("myRecalculate")
   .timeBased()
   .onWeekDay(ScriptApp.WeekDay.WEDNESDAY)
   .create();

user2970972 user2970972 · Accepted Answer · 2016-02-11T17:50:21

figured it out.

//AZ Script

function email() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var az = ss.getSheetByName("AZ");
  var lastrow = az.getLastRow();

  var data1 = az.getRange(2, 7, lastrow).getValues(); // part number column

  var ca = ss.getSheetByName("CA");
  var lastrow_ca = ca.getLastRow();

  var data2 = ca.getRange(2, 7, lastrow_ca).getValues(); // part number column

  var nh = ss.getSheetByName("NH");
  var lastrow_nh = nh.getLastRow();

  var data3 = nh.getRange(2, 7, lastrow_nh).getValues(); // part number column

  var report = ""
  var email ="";
  for(var i in data1){
    if(data1[i]!="") email += "<tr><td style='padding:5px'>" + data1[i] + "</td><td style='padding:5px'>" + data2[i] + "</td><td style='padding:5px'>" + data3[i] + "</td></tr>";
  }

  report = "<table style='width:100%'><tr bgcolor='#f1f1f1' style='padding:5px'><th>PHX Lotto</th><th>CA Lotto</th><th>NH Lotto</th></tr><tr>" + email + "</tr></table>";

  MailApp.sendEmail(Session.getActiveUser().getEmail(),
                "Cycle Count", report, {htmlBody: report});
}

Google Sheets Auto Email Script

1 Answers