Office 脚本示例方案：分析 Web 下载

在此方案中，你的任务是从公司网站分析下载报告。 此分析的目的是确定 Web 流量是来自美国还是来自世界其他地方。

同事将原始数据上传到工作簿。 每周的数据集都有自己的工作表。 还有“ 摘要 ”工作表，其中包含显示每周趋势的表格和图表。

你将开发一个脚本，用于分析活动工作表中的每周下载数据。 它将分析与每次下载关联的 IP 地址，并确定它是否来自美国。 答案将以布尔值 (“TRUE”或“FALSE”) 插入工作表中，条件格式将应用于这些单元格。 IP 地址位置结果将汇总到工作表中，并复制到摘要表中。

涵盖的脚本技能

• 文本分析
• 脚本中的子函数
• 条件格式
• 表格

设置说明

1. 将示例工作簿下载到 OneDrive。

   下载示例工作簿

2. 在 Excel 中打开工作簿。

3. 在“ 自动 ”选项卡下，选择“ 新建脚本 ”，并将以下脚本粘贴到编辑器中。

   function main(workbook: ExcelScript.Workbook) {
     /* Get the Summary worksheet and table.
       * End the script early if either object is not in the workbook.
       */
     let summaryWorksheet = workbook.getWorksheet("Summary");
     if (!summaryWorksheet) {
       console.log("The script expects a worksheet named \"Summary\". Please download the correct template and try again.");
       return;
     }
     let summaryTable = summaryWorksheet.getTable("Table1");
     if (!summaryTable) {
       console.log("The script expects a summary table named \"Table1\". Please download the correct template and try again.");
       return;
     }
   
     // Get the current worksheet.
     let currentWorksheet = workbook.getActiveWorksheet();
     if (currentWorksheet.getName().toLocaleLowerCase().indexOf("week") !== 0) {
       console.log("Please switch worksheet to one of the weekly data sheets and try again.")
       return;
     }
   
     // Get the values of the active range of the active worksheet.
     let logRange = currentWorksheet.getUsedRange();
   
     if (logRange.getColumnCount() !== 8) {
       console.log(`Verify that you are on the correct worksheet. Either the week's data has been already processed or the content is incorrect. The following columns are expected: ${[
         "Time Stamp", "IP Address", "kilobytes", "user agent code", "milliseconds", "Request", "Results", "Referrer"
       ]}`);
       return;
     }
     // Get the range that will contain TRUE/FALSE if the IP address is from the United States (US).
     let isUSColumn = logRange
       .getLastColumn()
       .getOffsetRange(0, 1);
   
     // Get the values of all the US IP addresses.
     let ipRange = workbook.getWorksheet("USIPAddresses").getUsedRange();
     let ipRangeValues = ipRange.getValues() as number[][];
     let logRangeValues = logRange.getValues() as string[][];
     // Remove the first row.
     let topRow = logRangeValues.shift();
     console.log(`Analyzing ${logRangeValues.length} entries.`);
   
     // Create a new array to contain the boolean representing if this is a US IP address.
     let newCol: (boolean | string)[][] = [];
   
     // Go through each row in worksheet and add Boolean.
     for (let i = 0; i < logRangeValues.length; i++) {
       let curRowIP = logRangeValues[i][1];
       if (findIP(ipRangeValues, ipAddressToInteger(curRowIP)) > 0) {
         newCol.push([true]);
       } else {
         newCol.push([false]);
       }
     }
   
     // Remove the empty column header and add proper heading.
     newCol = [["Is US IP"], ...newCol];
   
     // Write the result to the spreadsheet.
     console.log(`Adding column to indicate whether IP belongs to US region or not at address: ${isUSColumn.getAddress()}`);
     console.log(newCol.length);
     console.log(newCol);
     isUSColumn.setValues(newCol);
   
     // Call the local function to add summary data to the worksheet.
     addSummaryData();
   
     // Call the local function to apply conditional formatting.
     applyConditionalFormatting(isUSColumn);
   
     // Autofit columns.
     currentWorksheet.getUsedRange().getFormat().autofitColumns();
   
     // Get the calculated summary data.
     let summaryRangeValues = currentWorksheet.getRange("J2:M2").getValues();
   
     // Add the corresponding row to the summary table.
     summaryTable.addRow(null, summaryRangeValues[0]);
     console.log("Complete.");
     return;
   
     /**
      * A function to add summary data on the worksheet.
       */
     function addSummaryData() {
       // Add a summary row and table.
       let summaryHeader = [["Year", "Week", "US", "Other"]];
       let countTrueFormula =
         "=COUNTIF(" + isUSColumn.getAddress() + ', "=TRUE")/' + (newCol.length - 1);
       let countFalseFormula =
         "=COUNTIF(" + isUSColumn.getAddress() + ', "=FALSE")/' + (newCol.length - 1);
   
       let summaryContent = [
         [
           '=TEXT(A2,"YYYY")',
           '=TEXTJOIN(" ", FALSE, "Wk", WEEKNUM(A2))',
           countTrueFormula,
           countFalseFormula
         ]
       ];
       let summaryHeaderRow = currentWorksheet.getRange("J1:M1");
       let summaryContentRow = currentWorksheet.getRange("J2:M2");
       console.log("2");
   
       summaryHeaderRow.setValues(summaryHeader);
       console.log("3");
   
       summaryContentRow.setValues(summaryContent);
       console.log("4");
   
       let formats = [[".000", ".000"]];
       summaryContentRow
         .getOffsetRange(0, 2)
         .getResizedRange(0, -2).setNumberFormats(formats);
     }
   }
   /**
    * Apply conditional formatting based on TRUE/FALSE values of the Is US IP column.
    */
   function applyConditionalFormatting(isUSColumn: ExcelScript.Range) {
     // Add conditional formatting to the new column.
     let conditionalFormatTrue = isUSColumn.addConditionalFormat(
       ExcelScript.ConditionalFormatType.cellValue
     );
     let conditionalFormatFalse = isUSColumn.addConditionalFormat(
       ExcelScript.ConditionalFormatType.cellValue
     );
     // Set TRUE to light blue and FALSE to light orange.
     conditionalFormatTrue.getCellValue().getFormat().getFill().setColor("#8FA8DB");
     conditionalFormatTrue.getCellValue().setRule({
       formula1: "=TRUE",
       operator: ExcelScript.ConditionalCellValueOperator.equalTo
     });
     conditionalFormatFalse.getCellValue().getFormat().getFill().setColor("#F8CCAD");
     conditionalFormatFalse.getCellValue().setRule({
       formula1: "=FALSE",
       operator: ExcelScript.ConditionalCellValueOperator.equalTo
     });
   }
   /**
    * Translate an IP address into an integer.
    * @param ipAddress: IP address to verify.
    */
   function ipAddressToInteger(ipAddress: string): number {
     // Split the IP address into octets.
     let octets = ipAddress.split(".");
   
     // Create a number for each octet and do the math to create the integer value of the IP address.
     let fullNum =
       // Define an arbitrary number for the last octet.
       111 +
       parseInt(octets[2]) * 256 +
       parseInt(octets[1]) * 65536 +
       parseInt(octets[0]) * 16777216;
     return fullNum;
   }
   /**
    * Return the row number where the ip address is found.
    * @param ipLookupTable IP look-up table.
    * @param n IP address to number value.  
    */
   function findIP(ipLookupTable: number[][], n: number): number {
     for (let i = 0; i < ipLookupTable.length; i++) {
       if (ipLookupTable[i][0] <= n && ipLookupTable[i][1] >= n) {
         return i;
       }
     }
     return -1;
   }
   

4. 将脚本重命名为 “分析 Web 下载” 并保存它。

运行脚本

导航到任何 一周** 工作表，并运行 “分析 Web 下载” 脚本。 该脚本将在当前工作表上应用条件格式和位置标签。 它还将更新 “摘要” 工作表。

运行脚本之前

运行脚本后