0

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.

user5711693
  • 113
  • 5
  • 17
  • 1
    In general, you need to supply the exact error message being received, and indicate which line results in the error. For this case, I believe your issue is that you are attempting to concatenate a sql string and an object (`tabledef`), rather than just the BigQuery table identifier. – tehhowch Aug 18 '18 at 22:02
  • actually there aren't any examples that i could find, i have just wrote that based on the understanding i got from table definitions page of google api. regarding the sql generated, how can i find out where is the problem as i cant run it in big query - a temp table will be created once i run it. – user5711693 Aug 18 '18 at 23:40
  • also just further updated the code - that is the whole function that i am trying to get to work first before i write the rest. https://cloud.google.com/bigquery/external-data-drive#temporary-tables - mentions about temporary tables and how table definitions can be defined. – user5711693 Aug 18 '18 at 23:41
  • 2
    Edit your post to include the MCVE of your current error. – tehhowch Aug 19 '18 at 22:06
  • Updated the code and the current error. It seems like it is not recognising the tableName as external data source (temp table) and asking for dataset. I am checking further what could be the issue. – user5711693 Aug 20 '18 at 11:30

1 Answers1

1

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 + ";";
...
tehhowch
  • 9,645
  • 4
  • 24
  • 42
  • 1
    I updated this aswell: var jobConfig = BigQuery.newJobConfigurationQuery(); jobConfig.tableDefinitions = tableDef; jobConfig.query = sql; var queryR = BigQuery.Jobs.query(jobConfig, projectId); This gives a new error which is: Table name "tableName" cannot be resolved: dataset name is missing. – user5711693 Aug 19 '18 at 03:55