In this post, I demonstrate how to use the QUERY function and Google Apps Script and build multiple report views from a list.
How to Video:
Video Notes:
- Legacy Apps Script Editor Used in Video
- Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
- QUERY Function Documentation
Code in Video:
function onEdit(e) {
var range = e.range;
var spreadSheet = e.source;
var sheetName = spreadSheet.getActiveSheet().getName();
var column = range.getColumn();
var row = range.getRow();
var reportValue = e.value;
if(sheetName == 'REPORT' && column == 1 && row == 1)
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("DATA");
var reportSheet = ss.getSheetByName("REPORT");
var reportListSheet = ss.getSheetByName("REPORT LIST");
//GET SQL STATEMENT
var lastRowRLS = reportListSheet.getLastRow();
for(var i = 2; i <= lastRowRLS; i++)
{
if(reportValue == reportListSheet.getRange(i, 1).getValue())
{
var sqlStatement = reportListSheet.getRange(i, 2).getValue();
}
}
//GET LAST COLUMN ON DATASHEET
var lastColumn = 1;
while(dataSheet.getRange(1,lastColumn).isBlank() == false)
{
lastColumn = lastColumn + 1;
}
//GET LETTER OF COLUMN
var lastColumnLetter = columnReturnLetter(lastColumn);
//GET LAST ROW ON DATASHEET
var lastRowData = dataSheet.getLastRow();
var tableRange = 'DATA!A1:'+lastColumnLetter+lastRowData;
var query = '=QUERY('+tableRange+',\"'+sqlStatement+'\")';
var setResults = reportSheet.getRange(3, 1).setFormula(query);
}
}
function columnReturnLetter(number)
{
var temp = '';
var returnLetter = '';
while (number > 0)
{
temp = (number - 1) % 26;
returnLetter = String.fromCharCode(temp + 65) + returnLetter;
number = (number - temp - 1) / 26;
}
return returnLetter;
}
Related Posts
![Back Up Google Drive Files](https://codewithcurt.com/wp-content/uploads/2023/04/Back-Google-Drive-Files-WS-150x150.png)
![Embed Chart Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/11/Embed-Chart-GS-WS-150x150.png)
![Search Google Drive Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/11/Search-Google-Drive-GS-WS-150x150.png)
![Create Custom Menu Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/10/Create-Custom-Menu-GS-WS-150x150.png)
![Get and Set Value Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/07/Get-and-Set-Value-GS-WS-150x150.png)
![Create Data Validation Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Create-Data-Validation-GS-WS-150x150.png)
![Stock Trend Analysis Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Stock-Trend-Analysis-GS-WS-150x150.png)
![Scrape Amazon Website Googe Sheets](https://codewithcurt.com/wp-content/uploads/2022/03/Scrape-Amazon-Website-GS-WS-150x150.png)
![Alert Popups on Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/03/Alert-Popups-on-GS-WS-150x150.png)
![Combine Multiple Spreadsheets Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/02/Combine-Multiple-Spreadsheets-GS-WS-150x150.png)
![Last Row and Column Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Last-Row-and-Column-GS-WS-150x150.png)
![Time and Distance Map Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/02/Time-and-Distance-GS-WS-150x150.png)
![Create Dependent Dropdown Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/01/Create-Dependent-Dropdown-GS-WS-150x150.png)
![Call Rest API Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/01/Call-Rest-API-GS-WS-150x150.png)
![Google Doc From Apps Script](https://codewithcurt.com/wp-content/uploads/2021/12/Google-Doc-From-Apps-Script-WS-150x150.png)
![How to Clear, Delete, Insert, and Replace Row Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/12/How-to-Clear-Delete-Insert-and-Replace-Row-GS-WS-150x150.png)
![Email Last Update Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/12/Email-Last-Update-GS-WS-150x150.png)
![JSON Web Service Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/11/Web-Service-GS-WS-150x150.png)
![Scrape Website Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/02/Scrape-Website-GS-WS-150x150.png)
![Copy Row Google Sheet to Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/02/Copy-Row-Sheet-to-Sheet-WS-150x150.png)