Email Google Sheet cells and format -


background: teacher. gave test through forms. graded test using various background colors on each cell (which represented answer question student). each row of sheet has email address in column b.

problem: email entire row, including formatting, address in column b each student has record of answers , how graded them.

question: how can email row of data, including formatting?

i working following script, works emailing single cell without formatting:

`function sendemails() {   var sheet = spreadsheetapp.getactivesheet();   var startrow = 2;  // first row of data process   var numrows = 1;   // number of rows process   // fetch range of cells a2:b3   var datarange = sheet.getrange(startrow, 1, numrows, 2)   // fetch values each row in range.   var data = datarange.getvalues();   (i in data) {     var row = data[i];     var emailaddress = row[1];  // second column     var message = row[0];       // want whole row, including formatting.     var subject = "sending emails spreadsheet";     mailapp.sendemail(emailaddress, subject, message);     contentservice.createtextoutput("hello world!");   } }`

here is.

i decided add function makes little cleaner. you'll able adjust styles of output playing css styles. if keep commented lines can use them debugging. tested code them , looks good. let me know how works on emails.

  function sendemails()    {   var br='<br />';   var sheet=spreadsheetapp.getactivesheet();     var datarange=sheet.getdatarange();   var dataa=datarange.getvalues();   var backa=datarange.getbackgrounds();   //var s='';//please leave commented lines.  if needed future handy have   (var i=1;i<dataa.length;i++)    {     var emailaddress=dataa[i][1];       var message=formatrow(sheet.getname(),dataa[i],backa[i],dataa[0]);     var subject="sending emails spreadsheet";     //s+=br + '<strong>emailaddress:</strong>' + emailaddress + br + '<strong>subject:</strong>' + subject + br + message + '**************************************' + br;     mailapp.sendemail({to:emailaddress,subject:subject,htmlbody:message});    }   //var userinterface=htmlservice.createhtmloutput(s);   //spreadsheetapp.getui().showmodelessdialog(userinterface, 'email message') }

i noticed yellow background added section it.

   //assume timestamp,emailaddres,score,firstname,lastname,section... function formatrow(sheetname,rowa,rowbacka,titlea) {   var br='<br />';   var ss=spreadsheetapp.getactive();   var sh=ss.getsheetbyname(sheetname);   var html='';   if(rowa && rowbacka)   {     html='';     for(var j=0;j<rowa.length;j++)     {       switch(rowbacka[j])       {         case '#ff0000':           html+=br + '<span style="font-weight:600;font-size:20px;">' + titlea[j] + ':</span>' + br + '<span style="background-color:#ff0000;">' + rowa[j] + '</span>' + br;           break;         case '#ffff00':           html+=br + '<span style="font-weight:600;font-size:20px;">' + titlea[j] + ':</span>' + br + '<span style="background-color:#ffff00;">' + rowa[j] + '</span>' + br;           break;         case '#ffffff':           html+=br + '<span style="font-weight:600;font-size:20px;">' + titlea[j] + ':</span>' + br + '<span style="background-color:#ffffff;">' + rowa[j] + '</span>' + br;           break       }     }   }   return html; }

just reminder i'm using #ff0000 red don't change different shade without making change code.

in event 1 student's email gets eaten dog, might send 1 email.

function sendoneemail(firstname,lastname)  {   if(firstname && lastname)   {     var br='<br />';     var sheet=spreadsheetapp.getactivesheet();       var datarange=sheet.getdatarange();     var dataa=datarange.getvalues();     var backa=datarange.getbackgrounds();     //var s='';//please leave commented lines.  if needed future handy have     (var i=1;i<dataa.length;i++)      {       if(firstname==dataa[i][3] && lastname==dataa[i][4])       {         var emailaddress=dataa[i][1];           var message=formatrow(sheet.getname(),dataa[i],backa[i],dataa[0]);         var subject="sending emails spreadsheet";         //s+=br + '<strong>emailaddress:</strong>' + emailaddress + br + '<strong>subject:</strong>' + subject + br + message + '**************************************' + br;         mailapp.sendemail({to:emailaddress,subject:subject,htmlbody:message});        }     }     //var userinterface=htmlservice.createhtmloutput(s);     //spreadsheetapp.getui().showmodelessdialog(userinterface, 'email message')   } }

here's birds eye view of spreadsheet.

enter image description here


Comments

Post a Comment

Popular posts from this blog

What is happening when Matlab is starting a "parallel pool"? -

running parallel cpu processes in matlab starts command parpool() according documentation , function: [creates] special job on pool of workers, , [connects] matlab client parallel pool. this function takes bit of time execute, on order of 30 seconds. in other multi-cpu paradigms openmp, parallel execution seems totally transparent -- i've never noticed behavior analogous matlab (granted i'm not experienced parallel programming). so, happening between time parpool() called , when finishes executing? takes long? parallel computing toolbox enables run matlab code in parallel using several different paradigms (e.g. jobs , tasks, parfor , spmd , parfeval , batch processing), , run either locally (parallelised across cores in local machine) or remotely (parallelised across machines in cluster - either 1 own, or 1 in cloud). in of these cases, code run on matlab workers , copies of matlab without interactive desktop. if you're intending run on remo...
Read more

angular - DownloadURL return null in below code -

when change file updated in firebase storage when try downloadurl show null. filechange(event: any) { const imagefolder: string = this.employee.id; const filelist: filelist = event.target.files; const file: file = filelist[0]; const storageref = firebase.storage().ref().child(`${imagefolder}/profile.jpg`).put(file); console.log(storageref.snapshot.downloadurl); } the put() asynchronous. if want url after file uploaded have this: firebase.storage().ref().child(`${imagefolder}/profile.jpg`).put(file).then((snapshot) => { storageref = snapshot.downloadurl: console.log(snapshot.downloadurl); });
Read more

php - Cannot override Laravel Spark authentication with own implementation -

during login process of laravel spark need request token of remote api. api stores exact same users , passwords laravel spark application. therefore need username , non hashed password of user during authentication process. i thought overriding authenticated method solution problem. in routes/web.php override post /login endpoint , pointing endpoint own logincontroller: <?php namespace app\http\controllers\auth; use illuminate\support\facades\log; use illuminate\http\request; use laravel\spark\http\controllers\auth\logincontroller sparklogincontroller; class logincontroller extends sparklogincontroller { /** * create new login controller instance. * * @return void */ public function __construct() { parent::__construct(); } /** * handle successful authentication attempt. * * @param request $request * @param \illuminate\contracts\auth\authenticatable $user * @return response */ public fu...
Read more