Google Apps Script& Google AnalyticsAPI：无法从Google Spreadsheet列读取PropertyID [英] Google Apps Script & Google Analytics API: Can't read PropertyIDs from Google Spreadsheet columns

问题描述

我试图在脚本编辑器中使用此代码来读取Google Spreadsheet列中的查询，以便那些不知道Google Apps脚本的用户在想要更改查询时可以使用它，例如开始日期，结束日期，指标，维度等...代码可以成功读取开始日期，结束日期，指标，维度和过滤器。但是，我认为它不能从名为Report Configuration的表中识别accountId，webPropertyId和profileId。我收到了一个错误响应码：404。消息：未找到。你能帮我解决吗？以下是我从@Eudardo获得的代码：

  var LOG_SHEET_NAME ='非抽样报告日志'; 
 var ss = SpreadsheetApp.getActive（）; 
 var ui = SpreadsheetApp.getUi（）; 
 var sheet = ss.getSheetByName（'Report Configuration'）; 
 var startDateRange = sheet.getRange（10,5）; 
 var startDate = startDateRange.getValue（）; 
 var endDateRange = sheet.getRange（10,6）; 
 var endDate = endDateRange.getValue（）; 
 var metricsRange = sheet.getRange（10,8）; 
 var metrics = metricsRange.getValue（）; 
 var dimensionsRange = sheet.getRange（10,9）; 
 var dimensions = dimensionsRange.getValue（）; 
 var filtersRange = sheet.getRange（10,11）; 
 var filters = filtersRange.getValue（）; 
 
函数insertReport（）{
 var resource = {
'title'：'Medium Sale'，
'start-date'：startDate，
 'end-date'：endDate，
'metrics'：metrics，
'dimensions'：dimensions，
'filters'：filters，
}; 
 var ss = SpreadsheetApp.getActive（）; 
 var sheet = ss.getSheetByName（'Report Configuration'）; 
 var accountId = sheet.getRange（10,18）.getValue（）; 
 var webPropertyId = sheet.getRange（10,19）.getValue（）; 
 var profileId = sheet.getRange（10,20）.getValue（）; 
 
尝试{
 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）; 
} 
  

解决方案

没关系。我只是想出了为什么。这是因为我错误地使用了getRange（）。它应该是getRange（行，列），但我将其称为getRange（列，行）。傻我！ ：）

I'm trying to use this code in Script Editor to read the queries from Google Spreadsheet columns so that people who do not know Google Apps Scripts can use it when they want to change the queries like start-date, end-date, metrics, dimensions, etc...the codes can read start-date, end-date, metrics, dimensions, and filters successfully. However, I think it doesn't recognize the accountId, webPropertyId, and profileId from a sheet named "Report Configuration." I got an error "Response Code: 404. Message: Not Found". Could you help me on this? Below is the code I got from @Eudardo:

var LOG_SHEET_NAME = 'Unsampled Report Logs';
var ss = SpreadsheetApp.getActive();
var ui = SpreadsheetApp.getUi();
var sheet = ss.getSheetByName('Report Configuration');
var startDateRange = sheet.getRange(10,5); 
var startDate = startDateRange.getValue();
var endDateRange = sheet.getRange(10,6); 
var endDate = endDateRange.getValue();
var metricsRange = sheet.getRange(10,8); 
var metrics = metricsRange.getValue();
var dimensionsRange = sheet.getRange(10,9); 
var dimensions = dimensionsRange.getValue();
var filtersRange = sheet.getRange(10,11); 
var filters = filtersRange.getValue();

function insertReport() {
  var resource = {
        'title': 'Medium Sale',
        'start-date': startDate,
        'end-date': endDate,
        'metrics': metrics,
        'dimensions': dimensions,
        'filters': filters,
      };
   var ss = SpreadsheetApp.getActive();
   var sheet = ss.getSheetByName('Report Configuration');
   var accountId = sheet.getRange(10,18).getValue();
   var webPropertyId = sheet.getRange(10,19).getValue();
   var profileId = sheet.getRange(10,20).getValue();

  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);
}

解决方案

Never mind. I just figured out why. It's because of my mis-use of getRange(). It's supposed to be getRange(row,column), but I referenced it as getRange(column,row). Silly me! :)

这篇关于Google Apps Script& Google AnalyticsAPI：无法从Google Spreadsheet列读取PropertyID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！