google-apps-script - 将 Google 管理员用户报告导入 Google 表格

Question

我正在尝试将 Google 管理员帐户中的用户报告导入 Google 表格。导出到 Google 表格很简单，但我想运行一个 Google 脚本，将用户报告拉入现有的 Google 表格中。

标签： google-apps-scriptgoogle-sheetsgoogle-admin-sdk

Answer 1

尝试这个。

function cloneGoogleSheet(existingSheet, userReportSheet){
   //get source google sheet document
   var source = SpreadsheetApp.openById(userReportSheet);
   //get the source sheet on document
   var sourceSheet = source.getSheetByName('NameOfSheet');

   //get data range 
   var dataRange = sourceSheet.getDataRange();

   // get A1 notation for the range identification
   var A1Range = dataRange.getA1Notation();

   //get data within the specified range
   var data = dataRange.getValues();

   //specify the existing spreadsheet you want to pull data to
   var target = SpreadsheetApp.openById(existingSheet);

   //target sheet on your existing document 
   var targetSheet = target.getSheetByName('NameOfTargetSheet');

   //if there is already data on the target sheet, you may want to remove it 
   targetSheet.clear({contentsOnly: true});

   //now you set the target range of the values you get from your user report
   targetSheet.getRange(A1Range).setValues(data);
};