Skip to content

Instantly share code, notes, and snippets.

@oshliaer
Forked from rudimusmaximus/queryASpreadsheet.gs
Created January 8, 2017 22:02
Show Gist options
  • Save oshliaer/22fdbe11cff893e2723593114a2a0ae9 to your computer and use it in GitHub Desktop.
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
/**
* 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