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)

Comments

Post a Comment

Popular posts from this blog

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...
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' ), ...
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 ...
Read more