I am developing a community connector for Data Studio, directly linked to BigQuery, using Data Studio Advanced Services. However, I am not able to retrieve the service account credentials.
I have already copied and pasted the whole json file for the service account key as a string in SERVICE_ACCOUNT_CREDS var, as well as the whole string in the 'private_key' in the json in the SERVICE_ACCOUNT_KEY var, as well as the service account e-mail and billing project id as strings, respectively in the vars SERVICE_ACCOUNT_EMAIL and BILLING_PROJECT_ID. It fails when I try to authenticate when I run the getData() function.
Code (is the World Bank advanced services example, but with my credentials):
var cc = DataStudioApp.createCommunityConnector();
var scriptProperties = PropertiesService.getScriptProperties();
function isAdminUser() {
return false;
}
function getAuthType() {
return cc
.newAuthTypeResponse()
.setAuthType(cc.AuthType.NONE)
.build();
}
function getConfig(request) {
var config = cc.getConfig();
config
.newInfo()
.setId('info')
.setText(
'No configuration is required for this connector. Click connect to create a new data source.'
);
return config.build();
}
function getFields() {
var fields = cc.getFields();
var types = cc.FieldType;
var aggregations = cc.AggregationType;
fields
.newDimension()
.setId('country_name')
.setName('Country')
.setType(types.TEXT);
fields
.newDimension()
.setId('country_code')
.setName('Country Code')
.setType(types.TEXT);
fields
.newDimension()
.setId('indicator_name')
.setName('Indicator')
.setType(types.TEXT);
fields
.newDimension()
.setId('year')
.setName('Year')
.setType(types.YEAR);
fields
.newMetric()
.setId('value')
.setName('Value')
.setType(types.NUMBER)
.setIsReaggregatable(true)
.setAggregation(aggregations.SUM);
return fields;
}
function getSchema(request) {
return {
schema: getFields().build()
};
}
var SERVICE_ACCOUNT_CREDS = '{'+
'"type": "service_account",'+
'"project_id": "string for the project id",'+
'"private_key_id": "string for the private key id",'+
'"private_key": "-----BEGIN PRIVATE KEY-----the private key-----END PRIVATE KEY-----\n",'+
'"client_email": "[email protected]",'+
'"client_id": "the client id",'+
'"auth_uri": "https://accounts.google.com/o/oauth2/auth",'+
'"token_uri": "https://oauth2.googleapis.com/token",'+
'"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",'+
'"client_x509_cert_url": "url"'+
'}';
var SERVICE_ACCOUNT_KEY = 'private key string';
var SERVICE_ACCOUNT_EMAIL = '[email protected]';
var BILLING_PROJECT_ID = 'projectid';
/**
* Copy the entire credentials JSON file from creating a service account in GCP.
*/
function getServiceAccountCreds() {
return JSON.parse(scriptProperties.getProperty(SERVICE_ACCOUNT_CREDS));
}
function getOauthService() {
var serviceAccountCreds = getServiceAccountCreds();
var serviceAccountKey = serviceAccountCreds[SERVICE_ACCOUNT_KEY];
var serviceAccountEmail = serviceAccountCreds[SERVICE_ACCOUNT_EMAIL];
return OAuth2.createService('WorldBankHealthPopulation')
.setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
.setTokenUrl('https://accounts.google.com/o/oauth2/token')
.setPrivateKey(serviceAccountKey)
.setIssuer(serviceAccountEmail)
.setPropertyStore(scriptProperties)
.setCache(CacheService.getScriptCache())
.setScope(['https://www.googleapis.com/auth/bigquery.readonly']);
}
var FIELDS_WHITELIST = [
'country_name',
'country_code',
'indicator_name',
'year',
'value'
];
var BASE_SQL =
'SELECT {{FIELDS}} FROM `bigquery-public-data.world_bank_health_population.health_nutrition_population`';
function makeSQL(request) {
// Create an object of {[fieldName]: boolean} to use as a constant time lookup.
var simpleSet = FIELDS_WHITELIST.reduce(function(obj, field) {
obj[field] = true;
return obj;
}, {});
var requestFieldNames = request.fields.map(function(field) {
return field.name;
});
var fieldNames = FIELDS_WHITELIST.filter(function(fieldName) {
return simpleSet[fieldName];
});
var fieldsSQL = fieldNames.join(', ');
return BASE_SQL.replace('{{FIELDS}}', fieldsSQL);
}
function getData(request) {
var accessToken = getOauthService().getAccessToken();
var serviceAccountCreds = getServiceAccountCreds();
var billingProjectId = serviceAccountCreds[BILLING_PROJECT_ID];
var sql = makeSQL(request);
return cc
.newBigQueryConfig()
.setAccessToken(accessToken)
.setBillingProjectId(billingProjectId)
.setUseStandardSql(true)
.setQuery(sql)
.build();
}
Whenever I run either getOauthService() or getData() I get 'Cannot read property 'private key string' from null
Any help or tutorial fit for a 5-year old is highly appreciated.
In your getServiceAccountCreds function you are retrieving a field by using an object scriptProperties.getProperty(SERVICE_ACCOUNT_CREDS) which is SERVICE_ACCOUNT_CREDS. As per the Google Apps Script: Properties example, the only type of parameter expected is a String. For that reason, you should avoid parsing the JSON and use the Properties object created at the beginning of the code.
I recommend you to use the following code.
Replace your code
var SERVICE_ACCOUNT_CREDS = '{'+
'"type": "service_account",'+
'"project_id": "string for the project id",'+
'"private_key_id": "string for the private key id",'+
'"private_key": "-----BEGIN PRIVATE KEY-----the private key-----END PRIVATE KEY-----\n",'+
'"client_email": "[email protected]",'+
'"client_id": "the client id",'+
'"auth_uri": "https://accounts.google.com/o/oauth2/auth",'+
'"token_uri": "https://oauth2.googleapis.com/token",'+
'"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",'+
'"client_x509_cert_url": "url"'+
'}';
var SERVICE_ACCOUNT_KEY = 'private key string';
var SERVICE_ACCOUNT_EMAIL = '[email protected]';
var BILLING_PROJECT_ID = 'projectid';
/**
* Copy the entire credentials JSON file from creating a service account in GCP.
*/
function getServiceAccountCreds() {
return JSON.parse(scriptProperties.getProperty(SERVICE_ACCOUNT_CREDS));
}
For this code
var SERVICE_ACCOUNT_CREDS_PROPS = {
type: "service_account",
project_id: "string for the project id",
private_key_id: "string for the private key id",
private_key: "-----BEGIN PRIVATE KEY-----the private key-----END PRIVATE KEY-----\n",
client_email: "[email protected]",
client_id: "the client id",
auth_uri: "https://accounts.google.com/o/oauth2/auth",
token_uri: "https://oauth2.googleapis.com/token",
auth_provider_x509_cert_url: "https://www.googleapis.com/oauth2/v1/certs",
client_x509_cert_url: "url"
};
scriptProperties.setProperties(SERVICE_ACCOUNT_CREDS_PROPS);
var SERVICE_ACCOUNT_KEY = 'private_key';
var SERVICE_ACCOUNT_EMAIL = 'client_email';
var BILLING_PROJECT_ID = 'project_id';
/**
* Copy the entire credentials JSON file from creating a service account in GCP.
*/
function getServiceAccountCreds() {
return scriptProperties.getProperties();
}