如何使用Google Apps Script从Google AnalyticsManagement API请求非抽样报告？ [英] How to request unsampled report from Google Analytics Management API using Google Apps Script?
问题描述
我做了什么：
- 我拥有Google Analytics Premium
- 我已经授权OAuth2对于Apps脚本，请遵循以下说明： https://github.com/googlesamples/apps-script- oauth2
- 我在高级Google服务和开发者控制台上启用了Google Analytics API和云端硬盘API。
- 按照此说明请求非抽样报告： https://developers.google.com/analytics/devguides/config/mgmt/v3/mgmtReference/management/unsampledReports/insert#request
我的问题：
我在Google Apps脚本编辑器中编写了下面的代码，要求Google Analytics API提供非抽样报告。据我所知，如果它工作正常，它应该会触发谷歌分析界面上的非抽样请求。但是，我没有看到界面中的待定或已完成部分。当我运行代码时，什么都没有发生。我甚至没有看到任何错误。你能帮我解决我做错了什么吗？
这是我的代码：
function insertView（）var request = gapi.client.analytics.management.unsampledReports.insert（{'accountId'：'XXXXXX'，'webPropertyId'：'UA-XXXXXX-XX'，' 'profileId'：'XXXXXXXX'，'resource'：{'title'：'测试报告'，'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]）; //打印数据行。 sheetPrevious.getRange（2，1，request.getRows（）。length，headerNamesPrevious.length）.setValues（request.getRows（））; }}
我已经写过关于如何在此处执行操作的说明： http：/ /sophearychiv.com/how-to-pull-and-automate-unsampled-reports-from-google-analytics-into-google-spreadsheet/
以下是您可能需要尝试的工作版本。 >
现在你可以运行中的函数了sertReport（），这将使用API插入非抽样报告。请记住，就像我在上一个问题中告诉您的那样，这些可能需要几个小时才能完成。
在一段时间后运行 updateAllReports（）函数，它应该尝试获取报告的更新状态。
作为奖励，如果状态已完成，它会为您提供指向Google云端硬盘文件的链接，并将CSV中的数据导入另一张表单。
var LOG_SHEET_NAME ='Unsampled Report Logs';
var ss = SpreadsheetApp.getActive（）;
var ui = SpreadsheetApp.getUi（）;
函数insertReport（）{
var resource = {
'title'：'测试报告'，
'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';
尝试{
var request = Analytics.Management.UnsampledReports.insert（resource，accountId，webPropertyId，profileId）;
} catch（error）{
ui.alert（'执行非抽样报告查询错误'，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
]）;
//扫描LOG_SHEET_NAME并尝试更新任何报表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 ++）{
//如果数据是PENDING，我们尝试更新它的状态。希望它现在完成
//但如果（data [i] [0] == Session.getEffectiveUser（）。getEmail（）&& amp; amp;& 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.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（request.status =='COMPLETED'）{
importReportFromDrive（request.title ，request.driveDownloadDetails.documentId）;
}
}
}
//只写一次到电子表格，速度更快
dataRange.setValues（data）;
$ b函数importReportFromDrive（title，fileId）{
var file = DriveApp.getFileById（fileId）;
var csvString = file.getBlob（）。getDataAsString（）;
var data = Utilities.parseCsv（csvString）;
//为新工作表找到合适的名称
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）;
$ / code> PS：我为Google Analytics支持工作，正如Zig Mandel您可以随时与Google Analytics Premium支持部门联系，我们很乐意为您提供帮助。我们非常友好。
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.
这篇关于如何使用Google Apps Script从Google AnalyticsManagement API请求非抽样报告？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
