Use Apps Script URLFetchApp to access Google Datastore Data

Question

I want to experiment with Google Datastore via Apps Script because I have a current solution based on Google sheets that runs into timeout issues inherent in constantly transacting with Drive files. I've created a test project in Google cloud with a service account and enabled library MZx5DzNPsYjVyZaR67xXJQai_d-phDA33 (cGoa) to handle the Oauth2 work. I followed the guide to start it up here and got all the pertinent confirmation that it works with my token (and that removing the token throws an 'authentication failed prompt').

Now I want to start with a basic query to display the one entity I already put in. I can use the API Explorer here and run this query body:

{
   "query": {}
}

and get this result:

{
 "batch": {
"entityResultType": "FULL",
"entityResults": [
  {
    "entity": {
      "key": {
        "partitionId": {
          "projectId": "project-id-5200707333336492774"
        },
        "path": [
          {
            "kind": "Transaction",
            "id": "5629499534213120"
          }
        ]
      },
      "properties": {
        "CommentIn": {
          "stringValue": "My First Test Transaction"
        },
        "Status": {
          "stringValue": "Closed"
        },
        "auditStatus": {
          "stringValue": "Logged"
        },
        "User": {
          "stringValue": "John Doe"
        },
        "Start": {
          "timestampValue": "2017-08-17T18:07:04.681Z"
        },
        "CommentOut": {
          "stringValue": "Done for today!"
        },
        "End": {
          "timestampValue": "2017-08-17T20:07:38.058Z"
        },
        "Period": {
          "stringValue": "08/16/2017-08/31/2017"
        }
      }
    },
    "cursor": "CkISPGogc35whh9qZWN0LWlkLTUyMDA3MDcwODA1MDY0OTI3NzRyGAsSC1RyYW5zYWN0aW9uGICAgICAgIAKDBgAIAA=",
    "version": "1503004124243000"
  }
],
"endCursor": "CkISPGogc35wcm9qZWN0LWlkLTUyMDAxxDcwODA1MDY0OTI3NzRyGAsSC1RyYW5zYWN0aW9uGICAgICAgIAKDBgAIAA=",
"moreResults": "NO_MORE_RESULTS"
 }
}

I try to do the same thing with this code:

 function doGet(e)
  {
   var goa = cGoa.GoaApp.createGoa('Oauth2-Service-Account',

  PropertiesService.getScriptProperties()).execute(e);
    if(goa.hasToken()) {var token = goa.getToken();}

      var payload = {"query":{}}
            ; 

        var result = UrlFetchApp.fetch('https://datastore.googleapis.com/v1/projects/project-id-5200707333336492774:runQuery', 
                     {
                      method: "POST", 
                       headers: {authorization: "Bearer " + goa.getToken()}, 
         muteHttpExceptions : true,
                       payload: payload
                     });



        Logger.log(result.getBlob().getDataAsString());
          }

and get this error in the logger:

   "error": {
     "code": 400,
     "message": "Invalid JSON payload received. Unknown name \"query\": Cannot bind query parameter. 'query' is a message type. Parameters can only be bound to primitive types.",
     "status": "INVALID_ARGUMENT",
     "details": [
       {
         "@type": "type.googleapis.com/google.rpc.BadRequest",
         "fieldViolations": [
           {
             "description": "Invalid JSON payload received. Unknown name \"query\": Cannot bind query parameter. 'query' is a message type. Parameters can only be bound to primitive types."
           }
         ]
       }
     ]
   }
 }

If I try to use another word such as 'resource' or 'GqlQuery', I get this error:

  "error": {
     "code": 400,
     "message": "Invalid JSON payload received. Unknown name \"GqlQuery\": Cannot bind query parameter. Field 'GqlQuery' could not be found in request message.",
     "status": "INVALID_ARGUMENT",
     "details": [
       {
         "@type": "type.googleapis.com/google.rpc.BadRequest",
         "fieldViolations": [
           {
             "description": "Invalid JSON payload received. Unknown name \"GqlQuery\": Cannot bind query parameter. Field 'GqlQuery' could not be found in request message."
           }
         ]
       }
     ]
   }
 }

I can't tell from the API Documentation what my syntax is supposed to be. Can anyone tell me how to compile a functional request body from Apps Script to Datastore?

Also: Running the query with an empty body Creates this response: "error": { "code": 400, "message": "one of fields Query.query and Query.gql_query must be set", "status": "INVALID_ARGUMENT" } } — Nathaniel MacIver

TheAddonDepot TheAddonDepot · Accepted Answer · 2017-08-19T03:10:22

You need to set the contentType of your payload as well as stringify your JSON payload as follows:

var result = UrlFetchApp.fetch(
    'https://datastore.googleapis.com/v1/projects/project-id-5200707333336492774:runQuery',
    {
        'method':'post',
        'contentType':'application/json',
        'headers': {authorization: "Bearer " + goa.getToken()},
        'payload':JSON.stringify(payload)
    }
);

Use Apps Script URLFetchApp to access Google Datastore Data

1 Answers