Quora Ads Google Sheet Automated Reports

Overview

Supermetrics For Google Sheets connector for Quora Ads is a bit rough. So I set up a script that can be added to a Google Sheet to import data from Quora Ads and drop it directly into a Google Sheet.

Instructions

Script

Monthly Quora Ads Reporting

Weekly Quora Ads Reporting

Instructions

Contact Quora Support to request access to the Quora Ads API.
Once you've obtained access, you'll need to set up a Redirect URI...technically this script is set up so you won't need one. But I just set the Redirect URI to the link for the script in the following template (replace SCRIPT_ID with your script ID):

https://script.google.com/macros/d/SCRIPT_ID/usercallback

Name Google Sheet tab for data import Data Import.
Add your Account ID(s) to the var accountList. If you just have one account to pull data from, you can just delete the second ID placed there. You can (theoretically) add as many accounts as needed here. Data will be parsed out by account in the data delivered back to the sheet – but make sure that you include the accountName (and any other dimensions/metrics) in the fieldsVariable variable.
See instructions in green before the generateAuthUrl function.
If you want to make any other adjustments to the data output (after editing the fieldsVariable for dimensions/metrics), you can edit the initOutput and output variables based on the Quora Ads API documentation to edit as needed.
...I don't actually know if you need to do this, but I set up a trigger for generateAuthToken to fire off every 30 minutes just to make sure that the API key stays active...not sure if that's actually necessary, I just set it up that way and I didn't want to bother testing it without that running.
NOTE: The script below (mainDataImportScript) is just a simple script that pulls data for the last 7 days (see start/end dates) in aggregate and drops it into the sheet. Below the "Instructions" section below, I've added two more scripts: one that segments data by month and another by week. You can just take either of these and replace the mainDataImportScript with it. Or you can even just append these scripts to the bottom of the script file (with different tab names where the data will be delivered to, of course) and they'll run just fine.
Don't forget to set up a trigger for the mainDataImport or the other functions for importing data that you add/set up.
You can also edit the onOpen function to include your other functions to the Sheet Controls menu.

Script

function mainDataImportScript() {

var m=0;

while (m<3){

try{

//query params

var today = new Date();

var day = 1000*60*60*24;

var endDate = new Date(today.getTime() - (1*day));

var endDateFormat = Utilities.formatDate(endDate, 'GMT-5', 'yyyy-MM-dd');

Logger.log(endDateFormat);

var startDate = new Date(today.getTime() - (7*day));

var startDateFormat = Utilities.formatDate(startDate, 'GMT-5', 'yyyy-MM-dd');

Logger.log(startDateFormat);

var fields_variable = 'accountId,accountName,adId,adName,landingURL,impressions,clicks,spend';

//technical params

var page_limit = 25;

var accountList = ['XXXXXXXXXXXXXXX','YYYYYYYYYYYYYYY'];

var access_token = generateAuthToken();

Logger.log('Access Token = '+access_token);

var myHeaders = {

"Authorization": "Bearer "+access_token,

"Cookie": "m-ans_frontend_early_version=d2c38ecc12a5037e; m-b=S-7Q9szJjszBS0glqUAEyA==; m-b_lax=S-7Q9szJjszBS0glqUAEyA==; m-b_strict=S-7Q9szJjszBS0glqUAEyA==; m-css_v=main-7d0dbdebc84e4c04; m-early_v=8f7d03da0b7a24ae; m-lat=pcu1540tbbDWt5hPkguMog==; m-login=1; m-s=BRTm4MjPo7a-mYZMwMM8SQ==; m-theme=light; m-tz=300; m-uid=1389034950"

}

var requestOptions = {

method: 'GET',

headers: myHeaders,

followRedirects : true,

};

var numAccounts = accountList.length;

Logger.log(numAccounts+' accounts found.');

//establishes sheet

var sheet = SpreadsheetApp.getActive().getSheetByName('Data Import');

//builds array for fields

var fields_array = fields_variable.split(",");

//sets up array to build

var rows = [],

data;

for (l = 0; l < numAccounts; l ++){

var activeAccountId = accountList[l];

Logger.log(activeAccountId);

//get pagination values for iterative loop

var initOutput = UrlFetchApp.fetch("https://api.quora.com/ads/v0/accounts/"+activeAccountId+"?level=AD&fields="+fields_variable+"&sort=spend&order=DESCENDING&startDate="+startDateFormat+"&endDate="+endDateFormat+"&offset=0", requestOptions);

var initContent = initOutput.getContentText();

var initJson = JSON.parse(initContent);

try{

var totalAds = initJson.paging.totalResults;

}

catch(e){

var totalAds = initJson.data.length;

}

Logger.log(totalAds);

for (k = 0; k < totalAds; k += page_limit){

var output = UrlFetchApp.fetch("https://api.quora.com/ads/v0/accounts/"+activeAccountId+"?level=AD&fields="+fields_variable+"&sort=spend&order=DESCENDING&startDate="+startDateFormat+"&endDate="+endDateFormat+"&offset="+k, requestOptions);

var content = output.getContentText();

var json = JSON.parse(content);

var ads = json.data;

for (i = 0; i < ads.length; i++) {

data = ads[i];

var columns = [];

//builds out array: columns

for (j = 0; j < fields_array.length; j++) {

if (fields_array[j] == 'spend'){

columns.push((data[fields_array[j]]/10000));

}

else{

columns.push(data[fields_array[j]]);

}

rows.push(columns);

}

//clear import sheet

var clearRange = sheet.getRange(2, 1, 1000, fields_array.length);

clearRange.clearContent();

//reset headers

sheet.getRange('1:1').clearContent();

for (z=0; z<fields_array.length; z++){

sheet.getRange(1,z+1,1,1).setValue(fields_array[z]);

}

//log data

if(rows.length > 0) {

var dataRange = sheet.getRange(2, 1, rows.length, fields_array.length);

dataRange.setValues(rows);

sheet.getRange('I1').setValue(startDate);

}

else {

Logger.log('No data available.');

SpreadsheetApp.getActiveSpreadsheet().toast('No data available.','Error!');

}

break;

}

catch(e) {

Utilities.sleep(5000);

m++;

}

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('Sheet Controls')

.addItem('Import Quora Ads Data', 'mainDataImportScript')

.addToUi();

}

/**

* This script allows the stepping through of the Authorization Code Grant in

* order to obtain a refresh token.

*

* This script uses the out-of-band redirect URI, which is not part of the

* OAuth2 standard, to allow not redirecting the user. If this does not work

* with your API, try instead the OAuth playground:

* https://developers.google.com/oauthplayground/

*

* Execute script twice:

* Execution 1: will result in a URL, which when placed in the browser will

* issue a code.

* Execution 2: place the code in "CODE" below and execute. If successful a

* refresh token will be printed to the console.

*/

var CLIENT_ID = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

var CLIENT_SECRET = 'XXXXXXXXXXXXXXX-XXXXXXXXXXXXXXXXXXXXXXXXXXXX';

var SCOPES = ['ads_read'];

var AUTH_URL = 'https://www.quora.com/_/oauth/authorize';

var TOKEN_URL = 'https://www.quora.com/_/oauth/token';

// After execution 1, enter the OAuth code inside the quotes below:

var CODE = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

function main() {

if (CODE) {

generateRefreshToken();

} else {

generateAuthUrl();

}

/**

* Creates the URL for pasting in the browser, which will generate the code

* to be placed in the CODE variable.

*/

function generateAuthUrl() {

var payload = {

scope: SCOPES.join(' '),

// Specify that no redirection should take place

// This is Google-specific and not part of the OAuth2 specification.

redirect_uri: 'https://script.google.com/macros/d/1fuyXFMAbtCIBNsZYNdTYdFvwjqtVNIbyvPZKI4rYCG7zKTXJVrzrsNRy/usercallback',

response_type: 'code',

access_type: 'offline',

client_id: CLIENT_ID

};

var options = {payload: payload};

var request = UrlFetchApp.getRequest(AUTH_URL, options);

Logger.log(

'Browse to the following URL: \n' + AUTH_URL + '?' + request.payload);

}

/**

* Generates a refresh token given the authorization code.

*/

function generateRefreshToken() {

var payload = {

code: CODE,

client_id: CLIENT_ID,

client_secret: CLIENT_SECRET,

// Specify that no redirection should take place

// This is Google-specific and not part of the OAuth2 specification.

redirect_uri: 'https://script.google.com/macros/d/1fuyXFMAbtCIBNsZYNdTYdFvwjqtVNIbyvPZKI4rYCG7zKTXJVrzrsNRy/usercallback',

grant_type: 'authorization_code'

};

var options = {method: 'POST', payload: payload};

var response = UrlFetchApp.fetch(TOKEN_URL, options);

var data = JSON.parse(response.getContentText());

Logger.log(data);

if (data.refresh_token) {

var msg = 'Success! Refresh token: ' + data.refresh_token +

'\n\nThe following may also be a useful format for pasting into your ' +

'script:\n\n' +

'var CLIENT_ID = \'' + CLIENT_ID + '\';\n' +

'var CLIENT_SECRET = \'' + CLIENT_SECRET + '\';\n' +

'var REFRESH_TOKEN = \'' + data.refresh_token + '\';';

Logger.log(msg);

} else {

Logger.log(

'Error, failed to generate Refresh token: ' +

response.getContentText());

}

function generateAuthToken() {

var clientId = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

var clientSecret = 'XXXXXXXXXXXXXXX-XXXXXXXXXXXXXXXXXXXXXXXXXXXX';

var refreshToken = 'GET_FROM_CODE_IN_PREVIOUS_FUNCTION';

var grantType = 'refresh_token';

var tokenURL = 'https://www.quora.com/_/oauth/token';

var payload = {

refresh_token: refreshToken,

client_id: clientId,

client_secret: clientSecret,

redirect_uri: 'https://script.google.com/macros/d/1fuyXFMAbtCIBNsZYNdTYdFvwjqtVNIbyvPZKI4rYCG7zKTXJVrzrsNRy/usercallback',

grant_type: 'refresh_token'

};

var options = {method: 'POST', payload: payload};

var response = UrlFetchApp.fetch(tokenURL, options);

var data = JSON.parse(response.getContentText());

Logger.log(data);

return data.access_token;

}

Further Data Segmentation

Monthly Quora Ads Reporting

The script is currently configured to run YtD for 2021. to change the year, just edit the startDate variable.

function mainMonthlyDataImportScript() {

var m=0;

while (m<3){

try{

//query params

var fields_variable = 'accountName,campaignName,impressions,clicks,spend,conversions';

//technical params

var page_limit = 25;

var accountList = ['XXXXXXXXXXXXXXX'];

var access_token = generateAuthToken();

Logger.log('Access Token = '+access_token);

var myHeaders = {

"Authorization": "Bearer "+access_token,

"Cookie": "m-ans_frontend_early_version=d2c38ecc12a5037e; m-b=S-7Q9szJjszBS0glqUAEyA==; m-b_lax=S-7Q9szJjszBS0glqUAEyA==; m-b_strict=S-7Q9szJjszBS0glqUAEyA==; m-css_v=main-7d0dbdebc84e4c04; m-early_v=8f7d03da0b7a24ae; m-lat=pcu1540tbbDWt5hPkguMog==; m-login=1; m-s=BRTm4MjPo7a-mYZMwMM8SQ==; m-theme=light; m-tz=300; m-uid=1389034950"

}

var requestOptions = {

method: 'GET',

headers: myHeaders,

followRedirects : true,

};

var numAccounts = accountList.length;

Logger.log(numAccounts+' account(s) found.');

//establishes sheet

var sheet = SpreadsheetApp.getActive().getSheetByName('Monthly Data Import');

//builds array for fields

var fields_array = fields_variable.split(",");

//sets up array to build

var rows = [],

data;

//date params

var today = new Date();

var currentMonth = parseInt(Utilities.formatDate(today,'GMT-4','MM'));

Logger.log('Current Month = '+currentMonth);

//***LOOP 1*** iterate over months to today since 1/1/21 (var startDate below based on l=0)

for (l = 0; l < currentMonth; l++){

//establish blank array to aggregate data for each month. at end of each loop append this month's date and push to rows array above.

var monthIteratorArray = [];

//establish date variables

startDate = new Date(2021,l,1);

nextMonthStart = new Date(2021,l+1,1);

thisMonthEnd = new Date(nextMonthStart - (1000*60*60*24));

if (l == currentMonth-1){

Logger.log('Current Month in For Loop = '+l);

endDate = today;

}

else {

endDate = thisMonthEnd;

}

startDateFormat = Utilities.formatDate(startDate,'GMT-4','yyyy-MM-dd');

endDateFormat = Utilities.formatDate(endDate,'GMT-4','yyyy-MM-dd');

//***LOOP 2*** iterate over number of accounts in accountList variable

for (k = 0; k < numAccounts; k ++){

var activeAccountId = accountList[k];

Logger.log(activeAccountId);

//get pagination values for iterative loop

var initOutput = UrlFetchApp.fetch("https://api.quora.com/ads/v0/accounts/"+activeAccountId+"?level=CAMPAIGN&fields="+fields_variable+"&sort=spend&order=DESCENDING&startDate="+startDateFormat+"&endDate="+endDateFormat+"&offset=0", requestOptions);

var initContent = initOutput.getContentText();

var initJson = JSON.parse(initContent);

try{

var totalAds = initJson.paging.totalResults;

}

catch(e){

var totalAds = initJson.data.length;

}

Logger.log(totalAds);

//***LOOP 3*** loop over number of pages in results JSON data

for (j = 0; j < totalAds; j += page_limit){

var output = UrlFetchApp.fetch("https://api.quora.com/ads/v0/accounts/"+activeAccountId+"?level=CAMPAIGN&fields="+fields_variable+"&sort=spend&order=DESCENDING&startDate="+startDateFormat+"&endDate="+endDateFormat+"&offset="+j, requestOptions);

var content = output.getContentText();

var json = JSON.parse(content);

var ads = json.data;

//***LOOP 4*** loop over all ads on each page

for (i = 0; i < ads.length; i++) {

data = ads[i];

var columns = [];

//***LOOP 5*** builds out array: columns

for (h = 0; h < fields_array.length; h++) {

if (fields_array[h] == 'spend'){

columns.push((data[fields_array[h]]/10000));

}

else{

columns.push(data[fields_array[h]]);

}

columns.push(startDateFormat, endDateFormat);

monthIteratorArray.push(columns);

}

//concat contents of monthIteratorArray to rows and end loop

rows = rows.concat(monthIteratorArray);

}

fields_array.push("Start Date","End Date");

//clear import sheet

var clearRange = sheet.getRange(2, 1, rows.length, fields_array.length);

clearRange.clearContent();

//reset headers

sheet.getRange('1:1').clearContent();

for (z=0; z<fields_array.length; z++){

sheet.getRange(1,z+1,1,1).setValue(fields_array[z]);

}

//log data

if(rows.length > 0) {

var dataRange = sheet.getRange(2, 1, rows.length, fields_array.length);

dataRange.setValues(rows);

}

else {

Logger.log('No data available.');

SpreadsheetApp.getActiveSpreadsheet().toast('No data available.','Error!');

}

break;

}

catch(e) {

Utilities.sleep(5000);

m++;

}

Weekly Quora Ads Reporting

Just edit the firstWeekDate to adjust the start date for the weekly segmentation. End date is set to today. Can schedule script to run daily.

function mainWeeklyDataImportScript() {

var m=0;

while (m<3){

try{

//query params

var fields_variable = 'accountName,adName,impressions,clicks,spend,conversions';

//technical params

var page_limit = 25;

var accountList = ['XXXXXXXXXXXXXXX'];

var access_token = generateAuthToken();

Logger.log('Access Token = '+access_token);

var myHeaders = {

"Authorization": "Bearer "+access_token,

"Cookie": "m-ans_frontend_early_version=d2c38ecc12a5037e; m-b=S-7Q9szJjszBS0glqUAEyA==; m-b_lax=S-7Q9szJjszBS0glqUAEyA==; m-b_strict=S-7Q9szJjszBS0glqUAEyA==; m-css_v=main-7d0dbdebc84e4c04; m-early_v=8f7d03da0b7a24ae; m-lat=pcu1540tbbDWt5hPkguMog==; m-login=1; m-s=BRTm4MjPo7a-mYZMwMM8SQ==; m-theme=light; m-tz=300; m-uid=1389034950"

}

var requestOptions = {

method: 'GET',

headers: myHeaders,

followRedirects : true,

};

var numAccounts = accountList.length;

Logger.log(numAccounts+' account(s) found.');

//establishes sheet

var sheet = SpreadsheetApp.getActive().getSheetByName('Weekly Data Import');

//builds array for fields

var fields_array = fields_variable.split(",");

//sets up array to build

var rows = [],

data;

//date params

var today = new Date();

//firstWeekDate = 1/4/21 (first monday of podcasts is 11/2/2020)

var firstWeekDate = new Date(2020,10,2);

var dateDif = (today-firstWeekDate)/1000/60/60/24;

var weekDif = Math.ceil(dateDif/7);

Logger.log('Days since '+firstWeekDate+' = '+dateDif+'; weeks since start date = '+weekDif);

//***LOOP 1*** iterate over months to today since 11/2/20 (var firstWeekDate above based on l=0)

for (l = 0; l < weekDif; l++){

//establish blank array to aggregate data for each month. at end of each loop append this month's date and push to rows array above.

var weekIteratorArray = [];

//establish date variables

daysAdded = 1000*60*60*24;

Logger.log(l+' weeks added')

startDate = new Date(firstWeekDate.getTime() + 7 * l * daysAdded);

weeksEnd = new Date((startDate.getTime() + 7 * daysAdded) - daysAdded);

if (today < weeksEnd){

endDate = today;

}

else {

endDate = weeksEnd;

}

startDateFormat = Utilities.formatDate(startDate,'GMT-4','yyyy-MM-dd');

Logger.log(startDateFormat);

endDateFormat = Utilities.formatDate(endDate,'GMT-4','yyyy-MM-dd');

Logger.log(endDateFormat);

//***LOOP 2*** iterate over number of accounts in accountList variable

for (k = 0; k < numAccounts; k ++){

var activeAccountId = accountList[k];

Logger.log(activeAccountId);

//get pagination values for iterative loop

var initOutput = UrlFetchApp.fetch("https://api.quora.com/ads/v0/accounts/"+activeAccountId+"?level=AD&fields="+fields_variable+"&sort=spend&order=DESCENDING&startDate="+startDateFormat+"&endDate="+endDateFormat+"&offset=0", requestOptions);

var initContent = initOutput.getContentText();

var initJson = JSON.parse(initContent);

try{

var totalAds = initJson.paging.totalResults;

}

catch(e){

var totalAds = initJson.data.length;

}

Logger.log(totalAds);

//***LOOP 3*** loop over number of pages in results JSON data

for (j = 0; j < totalAds; j += page_limit){

var output = UrlFetchApp.fetch("https://api.quora.com/ads/v0/accounts/"+activeAccountId+"?level=AD&fields="+fields_variable+"&sort=spend&order=DESCENDING&startDate="+startDateFormat+"&endDate="+endDateFormat+"&offset="+j, requestOptions);

var content = output.getContentText();

var json = JSON.parse(content);

var ads = json.data;

//***LOOP 4*** loop over all ads on each page

for (i = 0; i < ads.length; i++) {

data = ads[i];

var columns = [];

//***LOOP 5*** builds out array: columns

if(data['adName'].indexOf("Podcast")>-1 && data['impressions']>0){

for (h = 0; h < fields_array.length; h++) {

if (fields_array[h] == 'spend'){

columns.push((data[fields_array[h]]/10000));

}

else{

columns.push(data[fields_array[h]]);

}

else{

continue;

}

columns.push(startDateFormat, endDateFormat);

weekIteratorArray.push(columns);

}

//concat contents of weekIteratorArray to rows and end loop

rows = rows.concat(weekIteratorArray);

}

fields_array.push("Start Date","End Date");

//clear import sheet

var clearRange = sheet.getRange(2, 1, rows.length, fields_array.length);

clearRange.clearContent();

//reset headers

sheet.getRange('1:1').clearContent();

for (z=0; z<fields_array.length; z++){

sheet.getRange(1,z+1,1,1).setValue(fields_array[z]);

}

//log data

if(rows.length > 0) {

var dataRange = sheet.getRange(2, 1, rows.length, fields_array.length);

dataRange.setValues(rows);

}

else {

Logger.log('No data available.');

SpreadsheetApp.getActiveSpreadsheet().toast('No data available.','Error!');

}

break;

}

catch(e) {

Utilities.sleep(5000);

m++;

}