1
var tenant_id = '<目錄識別碼>';
2
var client_id = '<應用程式識別碼>';
3
var client_secret = '<用戶端密碼>';
4
var sheet_name = '<工作表名稱>';
5
// 要讀取的使用者欄位
6
var cols = {
7
  'displayName': '姓名',
8
  'userPrincipalName': '信箱',
9
  'lastPasswordChangeDateTime': '密碼修改日',
10
  'createdDateTime': '帳號建立日',
11
  'accountEnabled': '帳號啟用'
12
};
13

14
function onOpen() {
15
  var ui = SpreadsheetApp.getUi();
16
  ui.createMenu('功能')
17
    .addItem('更新清單', 'update')
18
    .addToUi();
19
}
20

21
function update() {
22
  // 取得AAD token
23
  var url = `https://login.microsoftonline.com/${tenant_id}/oauth2/v2.0/token`;
24
  var options = {
25
    method: 'POST',
26
    payload: {
27
      client_id: client_id,
28
      client_secret: client_secret,
29
      scope: 'https://graph.microsoft.com/.default',
30
      grant_type: 'client_credentials',
31
    }
32
  }
33
  var response = UrlFetchApp.fetch(url, options);
34
  var token = JSON.parse(response.getContentText())["access_token"];
35

36
  // 讀取AAD使用者清單
37
  var url = 'https://graph.microsoft.com/v1.0/users?$select=' + Object.keys(cols).join(',') + '&$top=999';
38
  var options = {
39
    method: 'GET',
40
    headers: { Authorization: 'Bearer ' + token }
41
  }
42
  var response = UrlFetchApp.fetch(url, options);
43
  var items = JSON.parse(response.getContentText())["value"];
44

45
  // 寫入Google Sheet
46
  var ss = SpreadsheetApp.getActiveSpreadsheet();
47
  var s = ss.getSheetByName(sheet_name);
48
  s.getRange('A:Z').clearContent();
49
  s.appendRow(Object.values(cols));
50
  items.forEach(function (item) {
51
    var result = [];
52
    for (const i of Object.keys(cols)) {
53
      result.push(item[i]);
54
    }
55
    s.appendRow(result);
56
  });
57
}