Access BigQuery with Google Spreadsheet

Question

I look on the Internet to see how I can import big load data into google spreadsheet. I found this sample application, but the API does not work at the same level, and I do not find how to request API2 or API @ beta1 in appscript.

function runQuery() { var ss = SpreadsheetApp.getActive(); var range = ss.getRangeByName('query'); var query = range.getCell(1, 1).getValue(); //var results = bigquery.query(query); var header = ss.getRangeByName('header'); header.clearContent(); var output = ss.getRangeByName('output'); output.clearContent(); for (var i = 0; i < results.fields.length; i++) { var field = results.fields[i]; header.getCell(1, 1 + i).setValue(field.id); } for (var i = 0; i < results.rows.length; i++) { var row = results.rows[i].f; for (var j = 0; j < row.length; ++j) { output.getCell(1 + i, 1 + j).setValue(row[j].v); } } } 

Thanks in advance for your ideas,

Gq

Answer 1

UPDATE: We just added a new BigQuery + Apps Script tutorial in which you can find the answer to this question here: https://developers.google.com/apps-script/articles/bigquery_tutorial

@GQuery: We recently updated the version of the application to access the latest version of the BigQuery API (v2). Here's a simple example to run, will display the results in the application log. We are working on updating the AppScript / BigQuery documentation.

 function runQuery() { var projectId = 'YOUR PROJECT'; var sql = 'select word, word_count from publicdata:samples.shakespeare limit 100'; var queryResults; // Run the query try { queryResults = BigQuery.Jobs.query(projectId, sql); } catch (err) { Logger.log(err); return; } // Loop until successful job completion while (queryResults.getJobComplete() == false) { try { queryResults = BigQuery.Jobs.getQueryResults(projectId, queryResults.getJobReference().getJobId()); } catch (err) { Logger.log(err); return; } } var tableRows = queryResults.getRows(); for (var i = 0; i < tableRows.length; i++) { var rowString = ''; var cols = tableRows[i].getF(); for (var j = 0; j < cols.length; j++) { rowString += cols[j].getV() + '\t'; } Logger.log(rowString); 

Answer 2

I have no reputation to comment on hurricaneditka16. So I posted this answer: This line

 queryResults = BigQuery.Jobs.query(projectId, sql); 

Should be replaced by

 .query( resource, projectId); 

Resource is a small sql conversion you used earlier. Try this conversion and it will work.

 function getResource(sql) { var resource = '{"query": "sql"}' resource = resource.replace('sql', sql); return resource }