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, Aug 17 '17 at 23:50

score 2 · Accepted Answer · answered Aug 19 '17 at 03:10

2

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)
    }
);

answered Aug 19 '17 at 03:10

TheAddonDepot

8,408
2
20
30

You did it, Dimu Designs! Thanks! It works good now! So, is there a reference for this API that I didn't find? Google's API page didn't make that clear... – Nathaniel MacIver Aug 21 '17 at 15:30
Sending requests and receiving responses over HTTP is universal to web development in general so the Apps Script documentation kinda glosses over it. But it does cover the topic a wee bit under the UrlFetchApp documentation: https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app – TheAddonDepot Aug 22 '17 at 02:09
If you really want to delve deeper into the topic you can try tackling the following guide: https://code.tutsplus.com/tutorials/http-the-protocol-every-web-developer-must-know-part-1--net-31177 It's a lot to chew on but you'll come away with a better understanding of how http works once you muddle through it. – TheAddonDepot Aug 22 '17 at 02:14

Use Apps Script URLFetchApp to access Google Datastore Data

1 Answers1

Linked