What I have done:
- I have Google Analytics Premium
- I have authorized OAuth2 for Apps Script by following this instruction: https://github.com/googlesamples/apps-script-oauth2
- I have enabled Google Analytics API and Drive API on Advanced Google Services and on the Developers Console.
- I'm trying to follow this instruction to request the unsampled report: https://developers.google.com/analytics/devguides/config/mgmt/v3/mgmtReference/management/unsampledReports/insert#request
My problem:
I've written the code below on Google Apps Script editor to request unsampled report from Google Analytics API. As far as I know, if it works correctly, it's supposed to trigger the unsampled request on Google Analytics interface. However, I don't see it in the Pending or Completed section in the interface. And when I ran the code, nothing happened. I didn't even see any error. Could you please help on what I have done wrong? Thank you in advance.
Here is my code:
function insertView()
var request = gapi.client.analytics.management.unsampledReports.insert(
{
'accountId': 'XXXXXX',
'webPropertyId': 'UA-XXXXXX-XX',
'profileId': 'XXXXXXXX',
'resource': {
'title': 'A test Report',
'start-date': '2016-03-31',
'end-date': '2016-04-04',
'metrics': 'ga:itemRevenue',
'dimensions': 'ga:date'
}
});
return request;
}
}
function outputToSpreadsheetNext(request) {
var sheetId = '1RSkx8n-YRMq7Cnco-mvC83bJPKSnsb3QPx3BItAWmN8';
var sheetPrevious= SpreadsheetApp.openById(sheetId).getSheets()[0];
var headerNamesPrevious = []
for (var i = 0, header; header = request.getColumnHeaders()[i]; ++i) {
headerNamesPrevious.push(header.getName());
}
sheetPrevious.getRange(1, 1, 1, headerNamesPrevious.length)
.setValues([headerNamesPrevious]);
// Print the rows of data.
sheetPrevious.getRange(2, 1,request.getRows().length,headerNamesPrevious.length)
.setValues(request.getRows());
}
}
I have written instructions on how to do it here: http://sophearychiv.com/how-to-pull-and-automate-unsampled-reports-from-google-analytics-into-google-spreadsheet/
Here's a working version you might want to try.
Instructions
- Create a new Google Spreadsheet.
- Copy the content bellow into a new script
- Go into Resources > Advanced Google Services
- Enable the Google Analytics API toggling it to ON
- Click the Google Developer Console link still on the Advanced Google Services dialog
- From the Cloud API Manager find and Enable the Analytics API
Now you can run the function insertReport(), this will insert an Unsampled Report using the API. Remember that just like I told you in the previous question, these may take a few hours to process.
Run the updateAllReports() function after a while and it should try to get updated status for the reports.
As a bonus, if the status is complete it will give you the link to the file on Google Drive and also import the data from the CSV into a second sheet.
var LOG_SHEET_NAME = 'Unsampled Report Logs';
var ss = SpreadsheetApp.getActive();
var ui = SpreadsheetApp.getUi();
function insertReport() {
var resource = {
'title': 'A test Report',
'start-date': '2016-03-31',
'end-date': '2016-04-04',
'metrics': 'ga:itemRevenue',
'dimensions': 'ga:date'
};
var accountId = 'XXXXXXXX';
var webPropertyId = 'UA-XXXXXXXX-1';
var profileId = 'YYYYYYYY';
try {
var request = Analytics.Management.UnsampledReports.insert(resource, accountId, webPropertyId, profileId);
} catch (error) {
ui.alert('Error Performing Unsampled Report Query', error.message, ui.ButtonSet.OK);
return;
}
var sheet = ss.getSheetByName(LOG_SHEET_NAME);
if (!sheet) {
sheet = ss.insertSheet(LOG_SHEET_NAME);
sheet.appendRow(['User', 'Account', 'Web Property', 'View', 'Title', 'Inserted Time', 'Updated Time', 'Status', 'Id', 'File']);
sheet.getRange(1, 1, 1, 10).setFontWeight('bold');
}
sheet.appendRow([
Session.getEffectiveUser().getEmail(),
request.accountId,
request.webPropertyId,
request.profileId,
request.title,
request.created,
request.updated,
request.status,
request.id
]);
}
// Scans LOG_SHEET_NAME and tries to update any report that is PENDING
function updateAllReports() {
var sheet = ss.getSheetByName(LOG_SHEET_NAME);
var lastRow = sheet.getLastRow();
var dataRange = sheet.getRange(2,1, lastRow, 10);
var data = dataRange.getValues();
for (var i=0; i<data.length; i++) {
// If data is PENDING let's try to update it's status. Hopefully it's complete now
// but it may take up to 24h to process an Unsampled Reprot
if (data[i][0] == Session.getEffectiveUser().getEmail() && data[i][7] == 'PENDING') {
try {
var request = Analytics.Management.UnsampledReports.get(data[i][1], data[i][2], data[i][3], data[i][8]);
} catch (error) {
ui.alert('Error Performing Unsampled Report Query', error.message, ui.ButtonSet.OK);
return;
}
data[i] = [
Session.getEffectiveUser().getEmail(),
request.accountId,
request.webPropertyId,
request.profileId,
request.title,
request.created,
request.updated,
request.status,
request.id,
request.status == 'COMPLETED' ? DriveApp.getFileById(request.driveDownloadDetails.documentId).getUrl() : ''
];
// If data is Complete let's import it into a new sheet
if (request.status == 'COMPLETED') {
importReportFromDrive(request.title, request.driveDownloadDetails.documentId);
}
}
}
// Write only once to the spreadsheet this is faster
dataRange.setValues(data);
}
function importReportFromDrive(title, fileId) {
var file = DriveApp.getFileById(fileId);
var csvString = file.getBlob().getDataAsString();
var data = Utilities.parseCsv(csvString);
// Find a suitable name for the new sheet
var i=1;
var sheetName = title;
while (ss.getSheetByName(sheetName)) {
sheetName = title + ' ('+ i++ +')';
}
var sheet = ss.insertSheet(sheetName);
var range = sheet.getRange(1, 1, data.length, data[0].length);
range.setValues(data);
}
PS: I work for Google Analytics support, as "Zig Mandel" said in the comments feel free to reach out to Google Analytics Premium Support and we're happy to help. We're very friendly.
