Microsoft Ads Scripts Reading/Writing To Google Sheets

Question

Hello! I am trying to write a script that will ultimately take data from Microsoft Ads and record it in a centralized Google Sheet that my team can use. I'd then automate the script to run daily.

I am quite new to scripting and javascript especially, so I am taking baby steps to make this happen. I currently have a script written that is supposed to connect to my Google account, read a sheet, and log how many rows I indicated in the range. I have attached the relevant code at the bottom of my post.

I have used this resource to create authentication credentials and connect my accounts: https://learn.microsoft.com/en-us/advertising/scripts/examples/authenticating-with-google-services

I have used this resource to write the script and create the proper objects to connect to the Google API: https://learn.microsoft.com/en-us/advertising/scripts/examples/calling-google-services

The script runs successfully and does not report any errors. However, it does not return the requested information to the log. Due to my inexperience with both the language and the Microsoft Ads scripting environment, I do not understand why my script is not reporting the right information to the log. I would appreciate any help or guidance towards making this script work. Once I can verify that the script can find and read data on the spreadsheet, I'll proceed with making a version that can write to it.

Here is the script:

function main() {
// Set these fields based on the option you chose for getting an access token.
const credentials = {
accessToken: 'Removed For Privacy Purposes',
clientId: 'Removed For Privacy Purposes',
clientSecret: 'Removed For Privacy Purposes',
refreshToken: 'Removed For Privacy Purposes'
};
//Logger.log(credentials.accessToken);

var GoogleApis;
(function (GoogleApis) {
function createSheetsService(credentials) {
return createService("https://sheets.googleapis.com/$discovery/rest?version=v4", credentials);
}
GoogleApis.createSheetsService = createSheetsService;

function createDriveService(credentials) {
return createService("https://www.googleapis.com/discovery/v1/apis/drive/v3/rest", credentials);
}
GoogleApis.createDriveService = createDriveService;

function createGmailService(credentials) {
return createService("https://www.googleapis.com/discovery/v1/apis/gmail/v1/rest", credentials);
}
GoogleApis.createGmailService = createGmailService;

// Creation logic based on https://developers.google.com/discovery/v1/using#usage-simple
function createService(url, credentials) {
var content = UrlFetchApp.fetch(url).getContentText();
var discovery = JSON.parse(content);
var baseUrl = discovery['rootUrl'] + discovery['servicePath'];
var accessToken = getAccessToken(credentials);
var service = build(discovery, {}, baseUrl, accessToken);
return service;
}

function createNewMethod(method, baseUrl, accessToken) {
return (urlParams, body) => {
var urlPath = method.path;
var queryArguments = [];
for (var name in urlParams) {
var paramConfg = method.parameters[name];
if (!paramConfg) {
throw Unexpected url parameter ${name};
}
switch (paramConfg.location) {
case 'path':
urlPath = urlPath.replace('{' + name + '}', urlParams[name]);
break;
case 'query':
queryArguments.push(${name}=${urlParams[name]});
break;
default:
throw Unknown location ${paramConfg.location} for url parameter ${name};
}
}
var url = baseUrl + urlPath;
if (queryArguments.length > 0) {
url += '?' + queryArguments.join('&');
}
var httpResponse = UrlFetchApp.fetch(url, { contentType: 'application/json', method: method.httpMethod, payload: JSON.stringify(body), headers: { Authorization: Bearer ${accessToken} }, muteHttpExceptions: true });
var responseContent = httpResponse.getContentText();
var responseCode = httpResponse.getResponseCode();
var parsedResult;
try {
parsedResult = JSON.parse(responseContent);
} catch (e) {
parsedResult = false;
}
var response = new Response(parsedResult, responseContent, responseCode);
if (responseCode >= 200 && responseCode <= 299) {
return response;
}
throw response;
}
}

function Response(result, body, status) {
this.result = result;
this.body = body;
this.status = status;
}
Response.prototype.toString = function () {
return this.body;
}

function build(discovery, collection, baseUrl, accessToken) {
for (var name in discovery.resources) {
var resource = discovery.resources[name];
collection[name] = build(resource, {}, baseUrl, accessToken);
}
for (var name in discovery.methods) {
var method = discovery.methods[name];
collection[name] = createNewMethod(method, baseUrl, accessToken);
}
return collection;
}

function getAccessToken(credentials) {
if (credentials.accessToken) {
return credentials.accessToken;
}
var tokenResponse = UrlFetchApp.fetch('https://www.googleapis.com/oauth2/v4/token', { method: 'post', contentType: 'application/x-www-form-urlencoded', muteHttpExceptions: true, payload: { client_id: credentials.clientId, client_secret: credentials.clientSecret, refresh_token: credentials.refreshToken, grant_type: 'refresh_token' } });
var responseCode = tokenResponse.getResponseCode();
var responseText = tokenResponse.getContentText();
if (responseCode >= 200 && responseCode <= 299) {
var accessToken = JSON.parse(responseText)['access_token'];
return accessToken;
}
throw responseText;
}
})(GoogleApis || (GoogleApis = {}));

var sheetsApi = GoogleApis.createSheetsService(credentials);

function getValues(spreadsheetId, range, callback) {
try {
sheetsApi.client.sheets.spreadsheets.values.get({
spreadsheetId: spreadsheetId,
range: range,
}).then((response) => {
const result = response.result;
const numRows = result.values ? result.values.length : 0;
Logger.log(${numRows} rows retrieved.); // This should print 10 to the log. Why doesn't it?
if (callback) callback(response);
});
} catch (err) {
//document.getElementById('content').innerText = err.message;
return;
}
}
getValues("1GKDvfpY-Czf-HEicpetkpZUXpsk2LTo-Aoeb0E_cuh0", "A1:A10", "callback")
}
main()

Answer 1

Hey Nick!

Did you get to the bottom of this?

I ran into the same issue until I realised that I was looking for yesterdays stats for impressions over 0 and I didn't have any that's why the sheet was blank once I chose TODAY as the filter it gave me some data.