批次更新 Chromebook 自訂欄位：資產 ID、使用者、位置、附註
檔案下載：https://docs.google.com/spreadsheets/d/1ZLp2KasfIP2B0WZkqMDwgYrarlTd0NyQC2UkAcx_4eM/copy
Code.gs
/**
* 建立試算表自訂選單
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('🛠️ Chromebook 管理工具')
.addItem('1. 匯出/重整設備清單', 'showOuDialog') // 改為呼叫自訂對話方塊
.addItem('2. 執行批次更新', 'updateChromeDevices')
.addToUi();
}
/**
* 專門用來強制觸發授權的暫時函數
*/
function forceAuth() {
// 直接呼叫 API，不使用 try...catch 保護
AdminDirectory.Orgunits.list('my_customer', {type: 'all'});
}
/**
* 顯示自訂的 OU 選擇對話方塊 (稍微加高視窗以容納多層選單)
*/
function showOuDialog() {
var html = HtmlService.createHtmlOutputFromFile('OuDialog')
.setWidth(450)
.setHeight(400); // 增加高度
SpreadsheetApp.getUi().showModalDialog(html, '匯出選項：請選擇組織單位 (OU)');
}
/**
* 供 HTML 呼叫：取得網域內所有 OU 清單的原始結構
*/
function getOuList() {
var customerId = 'my_customer';
try {
// 撈取所有 OU 原始資料，保留 parentOrgUnitPath 等階層屬性
var response = AdminDirectory.Orgunits.list(customerId, {type: 'all'});
return { success: true, data: response.organizationUnits || [] };
} catch(e) {
return { success: false, error: e.message };
}
}
/**
* 供 HTML 呼叫：執行實際的匯出作業
*/
function executeExport(targetOU) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var customerId = 'my_customer';
var pageToken;
var devices = [];
// 設定標題列
sheet.clear();
var headers = ['Device ID (請勿更動)', '序號 (Serial Number)', '資產編號 (Asset ID)', '使用者 (User)', '位置 (Location)', '備註 (Notes)', '更新狀態'];
sheet.appendRow(headers);
sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold').setBackground('#f3f3f3');
// 設定 API 請求參數
var queryOptions = { maxResults: 200 };
if (targetOU !== '' && targetOU !== '/') {
queryOptions.orgUnitPath = targetOU;
}
// 取得裝置清單
do {
queryOptions.pageToken = pageToken;
var response = AdminDirectory.Chromeosdevices.list(customerId, queryOptions);
var list = response.chromeosdevices;
if (list) {
for (var i = 0; i < list.length; i++) {
var d = list[i];
devices.push([
d.deviceId,
d.serialNumber,
d.annotatedAssetId || '',
d.annotatedUser || '',
d.annotatedLocation || '',
d.notes || '',
''
]);
}
}
pageToken = response.nextPageToken;
} while (pageToken);
// 寫入資料
if(devices.length > 0) {
sheet.getRange(2, 1, devices.length, devices[0].length).setValues(devices);
sheet.autoResizeColumns(1, 6);
return '匯出完成！共找到 ' + devices.length + ' 筆裝置。';
} else {
return '找不到任何 ChromeOS 裝置，或該 OU 內無設備。';
}
}
/**
* 讀取試算表並批次更新 ChromeOS 裝置欄位 (保持不變)
*/
function updateChromeDevices() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var customerId = 'my_customer';
var updatedCount = 0;
if (data.length <= 1) return;
for (var i = 1; i < data.length; i++) {
var deviceId = data[i][0];
if (!deviceId) continue;
var assetId = data[i][2];
var user = data[i][3];
var location = data[i][4];
var notes = data[i][5];
var resource = {
annotatedAssetId: assetId,
annotatedUser: user,
annotatedLocation: location,
notes: notes
};
try {
AdminDirectory.Chromeosdevices.patch(resource, customerId, deviceId);
sheet.getRange(i + 1, 7).setValue('✅ 成功').setFontColor('green');
updatedCount++;
} catch (e) {
sheet.getRange(i + 1, 7).setValue('❌ 錯誤: ' + e.message).setFontColor('red');
}
}
SpreadsheetApp.getUi().alert('更新完成！共更新 ' + updatedCount + ' 筆裝置。');
}
OuDialog.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body { font-family: "Google Sans", Roboto, Arial, sans-serif; padding: 20px; color: #3c4043; }
p { margin-top: 0; font-size: 14px; margin-bottom: 10px; }
#cascading-container {
display: flex;
flex-direction: column;
gap: 8px;
max-height: 180px;
overflow-y: auto;
padding-right: 5px;
margin-bottom: 15px;
}
select { width: 100%; padding: 8px; font-size: 14px; border: 1px solid #dadce0; border-radius: 4px; outline: none; }
select:focus { border-color: #1a73e8; }
#path-display {
font-size: 12px;
color: #1a73e8;
background-color: #e8f0fe;
padding: 8px 12px;
border-radius: 4px;
margin-bottom: 20px;
word-break: break-all;
font-weight: 500;
}
.btn-container { text-align: right; }
button { padding: 8px 16px; font-size: 14px; font-weight: 500; border-radius: 4px; cursor: pointer; border: none; }
.btn-cancel { background-color: transparent; color: #1a73e8; margin-right: 8px; }
.btn-cancel:hover { background-color: #f1f3f4; }
.btn-submit { background-color: #1a73e8; color: white; }
.btn-submit:hover { background-color: #1557b0; }
.btn-submit:disabled { background-color: #e8eaed; color: #9aa0a6; cursor: not-allowed; }
#loading { font-size: 14px; color: #5f6368; text-align: center; margin-top: 50px;}
</style>
</head>
<body>
<div id="loading">🔄 正在載入並解析組織架構...</div>
<div id="form-content" style="display:none;">
<p>請依序選擇要匯出的階層：</p>
<div id="cascading-container"></div>
<div id="path-display">目標路徑：/ (預設範圍)</div>
<div class="btn-container">
<button class="btn-cancel" onclick="google.script.host.close()">取消</button>
<button class="btn-submit" id="btn-export" onclick="submitExport()">確定匯出</button>
</div>
</div>
<script>
let ouChildrenMap = {};
let currentTargetPath = '/';
window.onload = function() {
google.script.run
.withSuccessHandler(buildTreeAndInit)
.getOuList();
};
function buildTreeAndInit(response) {
if (!response.success) {
document.getElementById('loading').innerHTML = '❌ 載入失敗：<br>' + response.error;
return;
}
const data = response.data;
if (!data || data.length === 0) {
document.getElementById('loading').innerHTML = '❌ 找不到任何組織單位。';
return;
}
// 記錄所有抓到的 OU 路徑，用來判斷誰才是真正的「最高層」
const ouPaths = new Set(data.map(ou => ou.orgUnitPath));
const TOP_LEVEL_KEY = 'TOP_LEVEL';
ouChildrenMap[TOP_LEVEL_KEY] = [];
data.forEach(function(ou) {
let parent = ou.parentOrgUnitPath;
// 如果這個 OU 的父層不存在於我們抓到的資料中，
// 就代表它是您權限範圍內的最高層級
if (!parent || parent === '' || !ouPaths.has(parent)) {
ouChildrenMap[TOP_LEVEL_KEY].push(ou);
} else {
if (!ouChildrenMap[parent]) ouChildrenMap[parent] = [];
ouChildrenMap[parent].push(ou);
}
});
document.getElementById('loading').style.display = 'none';
document.getElementById('form-content').style.display = 'block';
// 從動態偵測到的最高層開始繪製選單
renderSelectLevel(TOP_LEVEL_KEY, 0);
}
function renderSelectLevel(parentPath, level) {
const container = document.getElementById('cascading-container');
// 清除更深層的選單
const existingSelects = container.querySelectorAll('.ou-select');
existingSelects.forEach(function(sel) {
if (parseInt(sel.dataset.level) >= level) {
sel.remove();
}
});
// 更新當前路徑
if (parentPath === 'TOP_LEVEL') {
currentTargetPath = '/';
} else {
currentTargetPath = parentPath;
}
updatePathDisplay();
const children = ouChildrenMap[parentPath];
if (!children || children.length === 0) return;
// 依名稱排序
children.sort((a, b) => a.name.localeCompare(b.name));
const select = document.createElement('select');
select.className = 'ou-select';
select.dataset.level = level;
const defaultOpt = document.createElement('option');
defaultOpt.value = parentPath;
if (level === 0) {
defaultOpt.text = '🌐 匯出全部權限範圍內的設備 (或選擇子層)';
} else {
defaultOpt.text = '📂 匯出此層級底下所有設備';
}
select.appendChild(defaultOpt);
children.forEach(function(child) {
const opt = document.createElement('option');
opt.value = child.orgUnitPath;
opt.text = '↳ ' + child.name;
select.appendChild(opt);
});
select.addEventListener('change', function() {
const selectedValue = this.value;
if (selectedValue === parentPath) {
const allSelects = container.querySelectorAll('.ou-select');
allSelects.forEach(function(sel) {
if (parseInt(sel.dataset.level) > level) sel.remove();
});
currentTargetPath = (parentPath === 'TOP_LEVEL') ? '/' : parentPath;
updatePathDisplay();
} else {
renderSelectLevel(selectedValue, level + 1);
}
});
container.appendChild(select);
container.scrollTop = container.scrollHeight;
}
function updatePathDisplay() {
const display = document.getElementById('path-display');
display.innerText = '目標路徑：' + currentTargetPath;
}
function submitExport() {
const btn = document.getElementById('btn-export');
btn.disabled = true;
btn.innerText = '匯出中，請稍候...';
google.script.run
.withSuccessHandler(function(resultMessage) {
alert(resultMessage);
google.script.host.close();
})
.withFailureHandler(function(error) {
alert('❌ 發生錯誤：' + error.message);
btn.disabled = false;
btn.innerText = '確定匯出';
})
.executeExport(currentTargetPath);
}
</script>
</body>
</html>
沒有留言:
張貼留言
歡迎大家一起留言討論！