My script takes too long and timeout at 30 minutes

[Dean Barrett]

Before i start rambling - i am NOT a programmer - just trying to automate some sheets at my work.

The premise of my script is to work through a 500 row spreadsheet, checking the email column for a match to an email address I obtain from another sheet.

When it finds a row that matches, it then checks a date in the same row to see if its within 14 days. 

If both criteria match, it takes 8 items from the row and puts them in variable that is HTML for sending in an email.

It does this for each row till it gets to the bottom of the sheet, it then sends an email.

It then loops round and does the same again for the next email address pulled from the other sheet.

My issue is that to run through the 500 rows looking for one email address once and generate an HTML email of 13 rows takes nearly 5 minutes.

I have 30 email addresses i want it to send emails to, but only get through the first 6 before the script times out at 30 minutes.

I'm guessing i'm doing something incredibly inefficient as i would have assume this was a fairly simple thing to do.

Any suggestions appreciated, or if any freelancers that would like a job and fix for me i'd be grateful !!



Dean

[Andrew Roberts]

If you post up your code we can take a look.

Or contact me directly at and...@roberts.net.

[Scott Bennett]

Are you working with it as an array in memory or are you grabbing each row individually? That would slow it down dramatically if you are working with each individual row.

Scott Bennett

Sent from my iPhone 

On Dec 17, 2021, at 6:33 AM, Dean Barrett <dean.b...@rolec.net> wrote:

Before i start rambling - i am NOT a programmer - just trying to automate some sheets at my work.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9ed56a0e-ae25-446f-84ea-cf9660f9bc3an%40googlegroups.com.

[CBMServices Web]

Dean,

The most likely issue for you is accessing the spreadsheets one row at a time or maybe one cell at a time. Each call to the SpreadsheetApp takes a couple of seconds regardless of how much data you ask for. A simple way to fix this is to pull the whole spreadsheet data in one call (use getValues() over the whole spreadsheet) and then just iterate over the array of data you receive to go through the content. That would be much faster for you.

If you are writing any info back to the spreadsheet, also try to combine the writes rather than doing them one at a time. I.e. use setValues() rather than using setValue() method.

If you need more help, then share your code and many will be able to provide advice.

--

[Dean Barrett]

Thanks for the swift replies guys

Script below - dont laugh - i've just cobbled together various bits of code i've found on google as i've gone along...

Your thoughts/direction appreciated.

Dean

-------------------------------------------------------

function CollatetoEmail()

{

//////////////////////////////////////////////CUSTOMER CARE COLLATE FOR EMAIL////////////////////////////////////////////////////////

 

//THIS AREA OPENS SEPERATE CCBUILDEREMAIL SPREADSHEET, GETS ONE EMAIL ADDRESS AT A TIME FOR TOTAL NUMBER OF EMAILS IN THE SHEET

//ADDRESS IS USED IN MAIN LOOP TO LOCATE CORRECT BUILDER EMAILS

//var ui = SpreadsheetApp.getUi();

var emailsheet = SpreadsheetApp.openById("1w-joV0U3QA-O2XU_BfVHuJaceLWwRLTS-U4fSJWRJmc").getActiveSheet();

var emailRows = emailsheet.getLastRow();

for (var h = 2; h < emailRows+1; h++) 

  

  {

var builderemail = emailsheet.getRange(h,1);  //row, column

var builderemailData = builderemail.getValue();

  

/////////////////////////////////////////////CREATION OF BUILDER SPECIFIC TABLE///////////////////////////////////////////////////////

//COMMON DATA

 

var ui = SpreadsheetApp.getUi();

//var ss = SpreadsheetApp.getActiveSpreadsheet();

 

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

//var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Electrical");  

var rowCounter=0  

var numRows = sheet.getLastRow()

var html='<html><head><font color=black><style>table, th, td {border: 1px solid gray; border-collapse:collapse;} th, td {padding: 10px;} </style>'

var html= html+'<font color=gray>This report generated for ' +builderemailData+ ' </font>'

var html=html+'<br><br><b><font color=red>ROLEC CUSTOMER CARE DAILY UPDATE</font><font color=black>&nbsp;-&nbsp;last 14 days</b><br><br>'

var html=html+'<img src=https://www.rolec.net/logo.png><br><br>'

var html=html+'<table border=1 style="width:90%"><tr bgcolor=#f76457 align=center><th width=75>Trade</th><th width=75>Date</th><th width=150>Your Reference</th><th width=50>Plot</th><th width=150>Customer</th><th width=300>Address</th><th width=50>Status</th></tr>'

 

var day = 24*3600*1000

var today = parseInt((new Date().setHours(0,0,0,0))/day);

// MAIN LOOP

 for (var i = 2; i < numRows+1; i++) 

  

  {

    

    var trade = sheet.getRange(i,4);  //row, column

    var tradeData = trade.getValue();

    var clientreference = sheet.getRange(i,7);  //row, column

    var clientreferenceData = clientreference.getValue();

    var builder = sheet.getRange(i,8);  //row, column

    var builderData = builder.getValue();

    var plot = sheet.getRange(i,9);  //row, column

    var plotData = plot.getValue();

    var site = sheet.getRange(i,10);  //row, column

    var siteData = site.getValue();

    var address = sheet.getRange(i,11);  //row, column

    var addressData = address.getValue();

    var customername = sheet.getRange(i,12);  //row, column

    var customernameData = customername.getValue();

    var dor = sheet.getRange(i,16);  //row, column

    var dorData = dor.getValue();

    var status = sheet.getRange(i,45);  //row, column

    var statusData = status.getValue();

    var checkemail = sheet.getRange(i,51);  //row, column

    var checkemailData = checkemail.getValue();

    ////////////////////////////// TIME/DATE FUNCTIONS //////////////////////////////

    var convertdate = new Date(dorData);

    var justDate = Utilities.formatDate(convertdate, "GMT", 'dd-MM-YY');

    var dataday = parseInt(dorData/day);

    var fourteendays = today-dataday

    var pending = dataday-today

    var EmailDate = new Date()

    //var EmailTime = EmailDate.toLocaleDateString(); // "12:35 PM", for instance

    var EmailTimeData = Utilities.formatDate(EmailDate, "GMT", 'dd-MM-YY'); 

    var to = builderemailData;

    var subject = ("Rolec Customer Care Update "+EmailTimeData);  

   ///////////////////////// POPULATE TABLE /////////////////////////    

   

        

  if (checkemailData == builderemailData)

  {

    

//now check for date range - nightly set at 14 days

if (fourteendays < 14)

{

//ui.alert("Date calc " + fourteendays + " " +justDate); 

//HTML row generated

    var html = html + '<tr bgcolor=#F2F5F7><td> ' + tradeData +' </td><td> ' + justDate +' </td><td> ' + clientreferenceData +' </td><td> ' + plotData + ' </td><td>' + customernameData + '</td><td>' + addressData + '</td><td>'  + statusData + '</td></tr>'; 

// Counter to only send emails with at least one line

rowCounter=rowCounter+1;

//ui.alert("Counter " + rowCounter + " " + builderemailData); 

//End period check

}

//end email match

}

  //LOOP BACK ROUND FOR TABLE CREATION

  }

//CLOSE TABLE

  var html = html + '</table>' 

 

//MAIL SEND - USES ADDRESS FROM CCBUILDEREMAIL SPREADSHEET   

// Check counter to only send if at least one row

if (rowCounter > 0)

{

    MailApp.sendEmail({

      to: to,

      subject: subject,

      htmlBody: html

      });

  //ui.alert("EMAIL Counter " + rowCounter); 

}

//CLOSE LOOP FROM CCBUILDEREMAIL SPREADSHEET

}     

//CLOSE FUNCTION

}

------------------------------------------------------------------

[CBMServices Web]

I updated your script not to make getValue calls so often and it should speed it up sufficiently. I did not modify the script any further not to change anything else.

 Try this:

function CollatetoEmail()

{

//////////////////////////////////////////////CUSTOMER CARE COLLATE FOR EMAIL////////////////////////////////////////////////////////

 

//THIS AREA OPENS SEPERATE CCBUILDEREMAIL SPREADSHEET, GETS ONE EMAIL ADDRESS AT A TIME FOR TOTAL NUMBER OF EMAILS IN THE SHEET

//ADDRESS IS USED IN MAIN LOOP TO LOCATE CORRECT BUILDER EMAILS

//var ui = SpreadsheetApp.getUi();

var emailsheet = SpreadsheetApp.openById("1w-joV0U3QA-O2XU_BfVHuJaceLWwRLTS-U4fSJWRJmc").getActiveSheet();

var emailRows = emailsheet.getLastRow();

for (var h = 2; h < emailRows+1; h++) 

  

{

  var builderemail = emailsheet.getRange(h,1);  //row, column

  var builderemailData = builderemail.getValue();

  

  /////////////////////////////////////////////CREATION OF BUILDER SPECIFIC TABLE///////////////////////////////////////////////////////

  //COMMON DATA

 

  var ui = SpreadsheetApp.getUi();

  //var ss = SpreadsheetApp.getActiveSpreadsheet();

 

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  var numRows = sheet.getLastRow();

  var numColumns = sheet.getLastColumn();

  var sheetData = sheet.getRange(1,1,numRows,numColumns).getValues();

  //var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Electrical");  

  var rowCounter=0  

  var html = '<html><head><font color=black><style>table, th, td {border: 1px solid gray; border-collapse:collapse;}';

  html += 'th, td {padding: 10px;} </style>';

  html += '<font color=gray>This report generated for ' +builderemailData+ ' </font>';

  html += '<br><br><b><font color=red>ROLEC CUSTOMER CARE DAILY UPDATE</font><font color=black>&nbsp;-&nbsp;last 14 days</b><br><br>';

  html +='<img src=https://www.rolec.net/logo.png><br><br>';

  html += '<table border=1 style="width:90%"><tr bgcolor=#f76457 align=center><th width=75>Trade</th><th width=75>Date</th>';

  html += '<th width=150>Your Reference</th><th width=50>Plot</th><th width=150>Customer</th><th width=300>Address</th>';

  html += '<th width=50>Status</th></tr>'

 

  var day = 24*3600*1000

  var today = parseInt((new Date().setHours(0,0,0,0))/day);

  // MAIN LOOP

  for (var i = 1; i < numRows; i++) 

  

  {

    

    var tradeData = sheetData[i][3];

    var clientreferenceData = sheetData[i][6];

    var builderData = sheetData[i][7];

    var plotData = sheetData[i][8];

    var siteData = sheetData[i][9];

    var addressData = sheetData[i][10];

    var customernameData = sheetData[i][11];

    var dorData = sheetData[i][15];

    var statusData = sheetData[i][44];

    var checkemailData = sheetData[i][50];

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9048dc37-7e44-4504-acf5-28fafbfa56f2n%40googlegroups.com.