Posts

GAS - All about Triggers

GAS - All about Triggers If you share a Google Sheet with someone and they write a script and set up triggers to run it, the script will only run using their account. If you remove that person from the Google Sheet, their triggers will stop working. To avoid this problem, ask others to write the script, but always set up the triggers using your own Google account.
Post a Comment
Read more

Send Email with Image/s in Mail Body Using GAS-2

Note: GmailApp - Respect cells formatting (line break but not bold text). MailApp - Don't respect cell formatting. It works better with HTML template.   function SendEmailUsingGAS () {   var subj = 'Test Email' ;   var mailBody = 'Test Email' ;   var img = DriveApp . getFileById ( '1fAalmjqjhjPpuDrcTBuHlt5NKCpe7Kvo' ). getAs ( 'image/png' );   var Emg ={ 'Logo' : img };   MailApp . sendEmail ({ to : 'ExcelSujeet@gmail.com' , subject : subj , htmlBody : mailBody + "<br><br>" + "<img src='cid:Logo' style='width:100px;height:100px'>" , inlineImages : Emg }); } URL's https://developers.google.com/apps-script/reference/mail/mail-app https://developers.google.com/apps-script/reference/mail/mail-app#sendemailrecipient,-subject,-body,-options Explanation This code is a Google Apps Script (GAS) function that sends an email using Gmail services and includes an inline image. Let...
Post a Comment
Read more

Send Email with Image/s in Mail Body Using GAS-1

Note: GmailApp - Respect cells formatting (line break but not bold text). MailApp - Don't respect cell formatting. It works better with HTML template.   GS Code function DesingTeam () {   var ss = SpreadsheetApp . getActiveSpreadsheet ();   var sht_Guidelines = ss . getSheetByName ( 'Guidelines' );   var sht_DesignTeam = ss . getSheetByName ( 'Design team' );   var lastRow ;   var i ;   var strMailBody ;   var strTask ;   var strDate ;   var strTo ;   var strCC ;   //Deadline Approaching   var strMailBodyTemp_1 = sht_Guidelines . getRange ( 'D2' ). getValue ();   var strSubject_1 = sht_Guidelines . getRange ( 'C2' ). getValue ();   var image_1 = DriveApp . getFileById ( '1iQYLBJQAWn_6_0rviBuFxjyN4IMBVrJz' ). getAs ( 'image/jpeg' );   var Eimage_1 ={ 'Logo' : image_1 };   var Ebody_1 = HtmlService . createTemplateFromFile ( "DeadlineApproaching" );   var name1 ;   //Reminder ...
Post a Comment
Read more

Read files name inside a folder (Spreadsheet Files Name, ID & SpreadSheet First Sheet Name) Using GAS

Read files name inside a folder (Spreadsheet Files Name, ID & SpreadSheet First Sheet Name) Using GAS function CollateData () {   const fls = DriveApp . getFolderById ( '1OHgxKOzer2GdsBtT8ZcATNeEkHhMraU7' ). getFiles ();   let fl ;   const ss = SpreadsheetApp . getActiveSpreadsheet ();   const sht = ss . getSheetByName ( 'Sheet2' );   let counter = 2 ;   let ss2 ;   while ( fls . hasNext ()){     fl = fls . next ();       if ( fl . getName ()!= "Master Sheet" && fl . getMimeType ()== "application/vnd.google-apps.spreadsheet" ){       sht . getRange ( counter , 2 ). setValue ( fl . getId ());       sht . getRange ( counter , 3 ). setValue ( fl . getName ());       ss2 = SpreadsheetApp . openById ( fl . getId ());       sht . getRange ( counter , 4 ). setValue ( ss2 . getSheets ()[ 0 ]. getName ());       counter = counter + 1 ;   ...
Post a Comment
Read more

Copy Paste Data, Remove Filter, Apply Filter, Delete Filtered Data

Copy Paste Data, Remove Filter, Apply Filter, Delete Filtered Data function   UpdateRationDistributionData (){    const   ss = SpreadsheetApp . getActiveSpreadsheet ();    const   sh1 = ss . getSheetByName ( 'Form Responses 1' );    const   lastRow1 = sh1 . getDataRange (). getLastRow ()- 1 ;    const   sh2 = ss . getSheetByName ( 'Ration Distribution Raw Data' );    const   lastRow2 = sh1 . getDataRange (). getLastRow ()- 1 ;    sh2 . getRange ( 'A1' ). setValue ( 'Member ID' );    sh2 . getRange ( 2 , 1 , lastRow2 ). setFormulaR1C1 ( '="M-" & LEFT(RC[32],4) &"-" & "Ration" & "-" & REPT(0,5-LEN(ROW()-1)) & ROW()-1' );    const   lastRow3 = sh2 . getDataRange (). getLastRow ();    sh2 . getRange ( 'A1:A' + lastRow3 ). copyTo ( sh2 . getRange ( 'A1' ), ...
Post a Comment
Read more

Copy and Paste Data Range

Copy and Paste Data Range function   CopyToFunction () {    const   ss = SpreadsheetApp . getActiveSpreadsheet ();    const   sh = ss . getSheets ()[ 0 ];    //sh.getRange('A1:C5').copyTo(sh.getRange('E1'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);    sh . getRange ( 'A1:C5' ). copyTo ( sh . getRange ( 'E1' ),  SpreadsheetApp . CopyPasteType . PASTE_NORMAL ,  false );    //sh.getRange('A1:C5').copyTo(sh.getRange('E1'), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);    //SpreadsheetApp.CopyPasteType.PASTE_NORMAL }
Post a Comment
Read more

Google Sheet - Query Function

 Google Sheet - Query Function = QUERY ( 'Rent Payment Summary'!I1:M4 , "Select DateDiff(M,L)/365 label DateDiff(M,L)/365 'Age' format DateDiff(M,L)/365 '#,##0'" , 1 ) = QUERY ( { ARRAYFORMULA ( LEFT ( I:I , 3 ) ) } , "Select * label Col1 'Code'" , 1 ) https://developers.google.com/chart/interactive/docs/querylanguage#Options = QUERY ( 'Form Responses 1'!A:E , "Select * where A is not Null" , 1 ) //Here A is column Name. Query(data, query statement, headers) = QUERY ( 'Rent Payment Summary'!I:L , "Select *" , 1 )
Post a Comment
Read more