-
-
Save oshliaer/22fdbe11cff893e2723593114a2a0ae9 to your computer and use it in GitHub Desktop.
Follow up to Totally UnScripted Episode 3: SQL like queries in Google Apps Script
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* quickly test our function | |
*/ | |
function test(){ | |
var result = queryASpreadsheet('1sPevvtTMSd9LUptX8qdsw4VJf07nOal_1qn9JLwO4fQ', | |
'Example Data', | |
'A1:C', | |
'SELECT A,B,C WHERE B < 7'); | |
var rows = result.length;//7 | |
var columns = result[0].length;//3 | |
// first clear anything in a sheet by that name in case running twice | |
var thisWorkbook = SpreadsheetApp.getActiveSpreadsheet(); | |
var outputSheet = thisWorkbook.getSheetByName('outputSheet'); | |
if (outputSheet) { | |
outputSheet.clear(); | |
} | |
else { | |
// insert a new sheet at the beginning | |
outputSheet = thisWorkbook.insertSheet('outputSheet' , 0 ); | |
} | |
// write to the outputSheet | |
var outputSheet = outputSheet.getRange(1,1,rows, columns).setValues(result); | |
return true; | |
}//end test | |
/** | |
* This function uses url fetch to get data from a spreadsheet using a query style | |
* | |
* @param {String} sheetId | |
* @param {String} sheetName | |
* @param {String} rangeSyntax a few example range syntax arguments: "A1:B10" - A range from cell A1 through B10", "5:7" - Rows 5-7, "D:F" - Columns D-F, "A:A70" - The first 70 cells in column A, "A70:A" - Column A from row 70 to the end, "B5:5" - B5 to the end of row 5, "D3:D" - D3 to the end of column D,"C:C10" - From the beginning of column C to C10 | |
* @param {String} queryString | |
* @return {Object[][]} dataTwoD is a JavaScript 2d array; rather this is an array of rows where the rows are arrays of values. | |
*/ | |
function queryASpreadsheet(sheetId, sheetName, rangeSyntax, queryString) { | |
//DriveApp.getRootFolder() | |
var url = 'https://docs.google.com/spreadsheets/d/'+sheetId+'/gviz/tq?'+ | |
'range=' + rangeSyntax + | |
'&tqx=out:csv' + | |
'&sheet='+sheetName+ | |
'&tq=' + encodeURIComponent(queryString); | |
var params = { | |
headers: { | |
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken() | |
}, | |
muteHttpExceptions: true | |
}; | |
var csvData = UrlFetchApp.fetch(url, params); //Returns Object — a map of Field Name to Value. The map usually has at least the following keys: url, method, contentType, payload, headers; however, trial an error in the editor showed me i could use .getContentText() | |
var csvString = csvData.getContentText(); | |
// This will create a 2 dimensional array of the format [[a, b, c], [d, e, f]] | |
var dataTwoD = Utilities.parseCsv(csvString); | |
return dataTwoD; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment