CODEWITHSUNDEEP
google-apps-scriptgoogle-bigquery
user5711693
user5711693

Reputation: 113

Querying tables that consist of google sheets data, google big query

I have been trying to query from a temporary table (table definitions) that consists of google sheets data but I keep getting error in code.gs. Below is my code: 

function test(){

var projectId = 'projectId';
var datasetId = 'datasetId';
var tableId = 'tableId';
var sheet_url = 'the_url_of_google_sheet';

var tableName = 'sheet_tab';
var tableProps = {
    externalDataConfiguration:{
    sourceUris: [sheet_url],
    googleSheetOptions:{
      skipLeadingRows: 1
    },
    sourceFormat: 'GOOGLE_SHEETS',
    autoDetect: true 
   }
}
var tableDef = {};
tableDef[tableName] = tableProps;
var sql = 'SELECT * FROM ' +tableName+';';

var jobConfig = BigQuery.newJobConfigurationQuery();
jobConfig.destinationTable = {projectId : projectId, datasetId : datasetId, 
tableId: tableId};
jobConfig.query = sql;
jobConfig.writeDisposition = 'WRITE_APPEND';
jobConfig.tableDefinitions = tableDef;

var queryR = BigQuery.Jobs.query(jobConfig, projectId)
}

The error message is 'TableName' could not be resolved, dataset name is missing.

Upvotes: 0

Views: 604

Answers (1)

tehhowch
tehhowch

Reputation: 9862

Your error when calling BigQuery originates in the invalid SQL you send. Your SQL is invalid because you concatenate a string ("SELECT * FROM") with an Object. You need to append the table identifier, not the table definition, to the SQL. (You also are missing a space between the "FROM" keyword and the added content.)

From the page you link, it gives an example (in Python) of constructing a table definition for a temporary table:

# Configure the external data source and query job
external_config = bigquery.ExternalConfig('GOOGLE_SHEETS')
sheet_url = "some url"
external_config.source_uris = [sheet_url]
external_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING')
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table_id = 'us_states'

job_config = bigquery.QueryJobConfig()
job_config.table_definitions = {table_id: external_config}  # <-- variable assignment in constructor

# Example query to find states starting with 'W'
sql = 'SELECT * FROM {} WHERE name like "W%"'.format(table_id)

query_job = client.query(sql, job_config=job_config)  # API request

The takeaway is that the table name to use in your SQL is the property in which you have stored the table definition. In your code, this would be "tab":

var tabledef = {
  tab: { // <--- the name of your temporary table is "tab"
    sourceUris: [sheet_url],
    googleSheetOptions:{
      skipLeadingRows: 1
    },
    sourceFormat: 'GOOGLE_SHEETS',
    autoDetect: true 
  }
}

Note that the Python code is able to use variable key assignment in the literal object constructor for its dict, while similar ("shorthand") is not valid in Apps Script (but can be done in newer JavaScript versions).

A more explicit way of identifying the table for a given query, to both you and anyone who needs to understand your code:

const tableName = /** .... */;
const tableProps = {
  ...
};
const tableDef = {};
tableDef[tableName] = tableProps;
var query = "SELECT * FROM " + tableName + ";";
...

Upvotes: 1

Related Questions