问题描述:

I want to create script which would collect information about changes in my spreadsheet and send it via e-mail e.g. once a 4 hours.

With the help of other turorials I was able to run such script

 function grabData(){

var ss = SpreadsheetApp.getActiveSpreadsheet()

var sheet = ss.getActiveSheet()

var cell = ss.getActiveCell();

var time = Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'dd-MM-yyyy')+' at '+Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'hh:mm');;

var dataUser1 = PropertiesService.getScriptProperties().getProperty('contentUser1');

if(dataUser1==null){dataUser1=''};

dataUser1+='\nSheet ' +sheet.getName()+' cell '+cell.getA1Notation()+' has been modify at '+time+' (new value = '+cell.getValue()+')';

PropertiesService.getScriptProperties().setProperty('contentUser1',dataUser1);

}

function sendReport(){

var dataUser1 = PropertiesService.getScriptProperties().getProperty('contentUser1');

if(dataUser1.length >1) {

MailApp.sendEmail('[email protected]', 'Update of Online orders ', dataUser1);

PropertiesService.getScriptProperties().setProperty('contentUser1','');

}

}

But it already happend that dataUser1 was error due "too big value".

I've also tried to build some aray to store each changed value description

function grabDataTemp()

{

var ss = SpreadsheetApp.getActiveSpreadsheet()

var sheet = ss.getActiveSheet()

var cell = ss.getActiveCell();

var time = Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'dd-MM-yyyy')+' at '+Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'hh:mm');;

var changeLog =[];

var logLength=changeLog.length;

if (logLength == null)

{

changeLog [0] ='\nSheet ' +sheet.getName()+' cell '+cell.getA1Notation()+' has been modify at '+time+' (new value = '+cell.getValue()+')';

changeLog [1]="";

}

changeLog [logLength+1] = ('\nSheet ' +sheet.getName()+' cell '+cell.getA1Notation()+' has been modify at '+time+' (new value = '+cell.getValue()+')');

return changeLog

}

function sendReportTemp(changeLog)

{

if(changeLog.length >1)

{

MailApp.sendEmail('[email protected]', 'Update of Online orders changeLog-TRIAL ', changeLog)

changeLog = [];

}

}

But it seems to be also not good solution. Is there any chance to do this task on aray like I tried to do it above?

网友答案:

Please check if you exceed a quota or limitation given in Quotas for Google Services.

An option that you can do is to use Class MailApp service in sending email. This service allows users to send emails with complete control over the content of the email and it's sole purpose is sending email.

Try going through the documentation for a more detailed information on the available methods and for sample codes that you can try.

In addition to that, you may also check G Suite email sending limits. It might also help.

网友答案:

Your first solution using scriptProperties is indeed subject to limitation in the string length these properties can have.

The sheet solution should work, I'd suggest to write a script that would convert the sheets content to an html table to make it more readable.

Use one row for each change in a single column to make the code below work without any change.

code example :

function sendReport(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var errorMsg = ss.getDataRange().getValues();
  var message = '<body><div style="font-family:arial,sans;font-size:12pt"><p>Change report</p>';  
  message+= '<table style="background-color:#fafafa;border-collapse:collapse;font-family:arial,sans;font-size:9pt;" border = 1 cellpadding = 5>';
  message+='<tr valign="top" bgcolor="#FFA" cellpadding=5><th>Number</th><th>description</th></tr>';
  var c = 1;//counter
  for(var n=0;n<errorMsg.length;n++){
    message+='<tr><td align="center">'+c+'</td><td>';
    message+=errorMsg[n][0]+'</td></tr>';
    c++;
  }
  message+='</table></body>'
  var hh = 100+35*errorMsg.length;
  if(hh>500){hh=500};
  var render = HtmlService.createHtmlOutput(message).setWidth(940).setHeight(hh);
  SpreadsheetApp.getUi().showModelessDialog(render, "preview");  
  MailApp.sendEmail(Session.getActiveUser().getEmail(),'change log','HTML ONLY',{'htmlBody':message})
}

相关阅读:
Top