I'm having trouble figuring out how to execute a simple delete query against a Big Query Table from an app script function. The query works when executing from the Big Query console. The error is coming back as : Unrecognized name: for the timestamp value I'm trying to pass in to string query.
The BigQuery api is loaded into app script as I can execute a simple select query with no parameter.
function RemoveRoles()
{
var sheet = SpreadsheetApp.getActive().getSheetByName("Config");
var value = sheet.getRange(2,2).getValue();
const projectId = 'xxxxx-346316';
const request = {
query: 'delete FROM `xxxxx-346316.Feedback.Role` where `Time` < `' value '`;',
useLegacySql: false
};
let queryResults = BigQuery.Jobs.query(request, projectId);
const jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
let sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
}
GoogleJsonResponseException: API call to bigquery.jobs.query failed with error: Unrecognized name: 2022-03-04 18:28:00
at [1:58]
CodePudding user response:
replace
const request = {
query: 'delete FROM `xxxxx-346316.Feedback.Role` where `Time` < `' value '`;',
useLegacySql: false
};
with
const request = {
query: 'delete FROM `xxxxx-346316.Feedback.Role` where `Time` < "' value '";',
useLegacySql: false
};