將attribute數據動態寫入到excel上

顯示效果：
I

大體思路：

• excel range name就設置為attribute_數字_類型，在創建template的時候，通過API得到這個event有幾個attribute，就創建幾列，同時還要根據不同的類型為下方的單元格設置格式
• pull的時候，根據attribute_數字_類型 的方式得到具體的range，然后把讀取到的數據填充到range里

創建attribute列顯示在excel上

// Add filtered attributes as columns with unique namesif (attributes[eventType.attributeEventType] && attributes[eventType.attributeEventType].length > 0) {attributes[eventType.attributeEventType].forEach((attribute, index) => {if(!attribute.showInEventDetails) return; // skip attributes that are not shown in event detailsconst attributeRange = headerRange.getOffsetRange(0, offset);attributeRange.values = [[attribute.attributeName]];attributeRange.format.font.bold = false;// Create a unique name by prefixing "attribute_(index+1)_attribute.type"const uniqueName = `attribute_${index + 1}_${attribute.type}`;sh.names.add(uniqueName, attributeRange);// Get the range for the values below the headerconst valueRange = attributeRange.getOffsetRange(1, 0);// Set format and data validation based on attribute typeswitch (attribute.type) {case 'Bool':// Set data validation for boolean valuesvalueRange.dataValidation.rule = {list: {inCellDropDown: true,source: "True,False",}};break;case 'Date':// Set date formatvalueRange.numberFormat = [['m/d/yyyy h:mm']];break;case "String":valueRange.numberFormat = [['@']];break;default:// Default case if no specific type handling is neededbreak;}offset++;});}

把對應的數據顯示在列的下面

for (let index = 0; index < attributes[eventSchema.attributeEventType].length; index++) {const attribute = attributes[eventSchema.attributeEventType][index];if (!attribute.showInEventDetails) continue; // skip attributes that are not shown in event details// Load the range for this attributelet attributeRange = sh.getRange(`attribute_${index + 1}_${attribute.type}`);try {await context.sync()} catch {console.log(`attribute ${attribute.attributeName} can't be found in spreadsheet ${sheetName}`);continue;}// Find the value for this attribute in the event itemlet attributeValue;const attr = ev.attributes?.items?.find(attr => attr.name === attribute.attributeName);if (attr) {switch (attr.type) {case "Boolean":attributeValue = attr.boolValue ? "TRUE" : "FALSE";break;case "String":attributeValue = attr.stringValue;break;case "List":attributeValue = attr.listValue;break;case "Date":attributeValue = this.timeZoneService.convertIsoToExcelDate(attr.dateValue);break;case "Int":attributeValue = attr.intValue;break;case "Float":attributeValue = attr.floatValue;break;default:attributeValue = ""; // Default value if no type matchesbreak;}}// Get the cell range for this attributeconst range = attributeRange.getOffsetRange(i + 1, 0);range.values = [[attributeValue || ""]]; // Set the cell value to the attribute value}

轉換ISO時間到excelDate的方法：

public convertIsoToExcelDate(isoString: string): number {if (!isoString) return null;const date = moment.tz(isoString, this.displayTimezone).toDate();const excelDateOffset = 25569;const excelDate = (date.getTime() / (24 * 60 * 60 * 1000)) + (moment(date).utcOffset() / (24 * 60)) + excelDateOffset;return excelDate;}