How do I link Google Sheet code to my paid Cloud Platform account using API-Key or OAuth2. I have a simple application script linked to a simple worksheet that works perfectly as long as I use it in "Free" mode. But I have not been able to get my Google Cloud Platform account API to work with my worksheet. Can anyone help me with this or tell me what I'm doing wrong?
What the worksheet does: Take "From-address", "To-address", and date-time from first three columns in a row. I then call maps.newDirectionFinder() with those three input parameters. I then update three other columns in the same row with the distance.value, duration.value, and duration_in_traffic.value.
Below is my code. When using "Maps.setAuthentication(<client-id>, <secret-key>)", I get an "Action not allowed" error in the line where Maps.newDirectionFinder() is called.
Note: my enabled APIs include: "Directions API", "Google Sheets API", "Distance Matrix API", "Maps Javascript API", and "Roads API".
Code (with keys removed):
/********************************************************************************
* Library of Direction Finding and Route Optimization routines from Google Maps
* Documentation of the Google Maps directionFinder functionality is here:
* https://developers.google.com/apps-script/reference/maps/direction-finder
********************************************************************************/
// To do:
// API Key (11/04/2018): <API-key>
/********************************************************************************
* calcDistanceTable:
********************************************************************************/
function calcDistanceTable() {
// var debug;
var mysheet = SpreadsheetApp.getActiveSheet();
var fromLocation = "test string";
var toLocation = "test string";
var lastRow = mysheet.getLastRow() + 1;
var directions;
var stringDate = "11/03/2018 22:00";//RR
var customDate = new Date('Thu Nov 02 2018 17:29:34 GMT-0700 (PDT)');//RR
var row = 1;
//AuthenticateMaps();//RR
// this next line doesn't work: there is no error message, but my GCP API Console shows no requests -- it seems to be ignored
// https://maps.googleapis.com/maps/api/staticmap?center=40.714%2c%20-73.998&zoom=12&size=400x400&key=<API-key>
// this next line failed with an error: TypeError: Attribute name "async" associated with an element type "script" must be followed by the ' = ' character. (line 32, file "getDistTime")
// <script async defer src="https://maps.googleapis.com/maps/api/js?key=<API-key>&callback=initMap" type="text/javascript"></script>
// this failed on the line where I call "directions = Maps.newDirectionFinder()" with this error message: Action not allowed (line 50, file "Code")
// Maps.setAuthentication("<Client-ID>","<Secret-key>");
do {
debug = mysheet.getRange(row, 1).getValues()[0][0];
// check column 5 ('E') to see if this has been run already for this row
if(mysheet.getRange(row, 5).getValues()[0][0] <= 0){
fromLocation = mysheet.getRange(row, 1).getValues()[0][0];
toLocation = mysheet.getRange(row, 2).getValues()[0][0];
stringDate=mysheet.getRange(row, 4).getValues()[0][0];//RR
customDate=new Date(stringDate);//RR
Logger.log("row: " + row + " From: " + fromLocation + " To: " + toLocation);
directions = Maps.newDirectionFinder()
.setOrigin(fromLocation)
.setDestination(toLocation)
.setDepart(customDate)//RR
.setMode(Maps.DirectionFinder.Mode.DRIVING)
//.key=<API-key> // this didn't work: ReferenceError: "xxxxxxx" is not defined. (line xx, file "Code")
//.key="<API-key>" // this didn't work: TypeError: Cannot find function getDirections in object <API-key>
.getDirections();
mysheet.getRange(row,6).setValue(directions.routes[0].legs[0].distance.value / 1000 * 0.621371);
mysheet.getRange(row,7).setValue(directions.routes[0].legs[0].duration.value / 60);
mysheet.getRange(row,8).setValue(directions.routes[0].legs[0].duration_in_traffic.value / 60);
temp = directions; // here to set a breakpoint to pause during debug
}
row++;
} while (row < lastRow);
}