将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;}