Google Apps Script and Big Query - tabledate.insertAll

Question

Have been struggling with this..... Google Apps Script and the Big Query API are working well however when I try to use BigQuery.Tabledata.insertAll I keep getting an error saying 'no such field'.

When I try to run the same thing through the Google API explorer it works fine. The documentation says the command is :

BigQuery.TableData.insertAll(TableDataInsertAllRequest resource, String projectId, String datasetId, String tableId)

I have constructed the TableDataInsertAllRequest resource as per the documentation https://developers.google.com/bigquery/docs/reference/v2/tabledata/insertAll and it looks like this :

{
  "kind": "bigquery#tableDataInsertAllRequest",
  "rows": 
  [
    {
      "json": 
      {
        "domain": "test",
        "kind": "another test"
      }
    }
  ]
}

This matches my table schema.

When I run the command the error returned is :

{
    "insertErrors": [
        {
            "index": 0,
            "errors": [
                {
                    "message": "no such field",
                    "reason": "invalid"
                }
            ]
        }
    ],
    "kind": "bigquery#tableDataInsertAllResponse"
}

As I say the same TableDataInsertAllRequest resource works fine in the API explorer (clicking Try It on the documentation page above) it just does not work through Apps Script.

Any help gratefully received.

Did you get the thing to work? Care to post your answer? Thanks! — Jie

Jesse Scherer Jesse Scherer · Accepted Answer · 2014-11-03T23:43:53

I've run into this too, and had somewhat better luck with this variation.

var rowObjects = [];
// Generally you'd do this next bit in a loop
var rowData = {};
rowData.domain = 'test';
rowData.kind = 'another test';
rowObjects.push(rowData);
// And at this point you'd have an array rowObjects with a bunch of objects
var response = BigQuery.Tabledata.insertAll({'rows': rowObjects}, projectId, datasetId, tableId);

Some things to note:

I don't indicate a kind -- it is implied by the call to insertAll()
I use dot notation (is that the right term?) rather than strings to stuff attributes into my "row objects"

I'm not sure which of these is the Secret Sauce. Anyways, in the end, the structure of the call looks about like this:

BigQuery.Tabledata.insertAll({'rows' : [
                                         {
                                           'domain' : 'test',
                                           'kind' : 'another test'
                                         }
                                       ]
                              },
                              projectId,
                              datasetId,
                              tableId);

Google Apps Script and Big Query - tabledate.insertAll

1 Answers