google-apps-script - Google Apps Script & Google Analytics API: 无法从Google电子表格列读取 PropertyIDs

  显示原文与译文双语对照的内容

我想在脚本编辑器中使用这个代码,以便不知道 Google application脚本的查询。 但是,我认为它无法识别名为"报表配置。"webPropertyId和profileId的工作表from我得到了一个错误"代码: 404.消息:找不到"。你可以帮助我? 下面是我从 @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);


}



时间: 原作者:

我才知道为什么。 这是因为我对 getRange()的使用。 它应该是 getRange(row,column),,但我把它引用为 getRange(column,row) 。 愚蠢的我) !

原作者:
...