Пример сценария Office: анализ веб-загрузки

В этом сценарии вам поручено проанализировать скачивание отчетов с веб-сайта вашей компании. Цель этого анализа — определить, поступает ли веб-трафик из США или из других мест в мире.

Ваши коллеги отправляют необработанные данные в книгу. Набор данных для каждой недели имеет собственный лист. Существует также лист Сводка с таблицей и диаграммой, на которые отображаются тенденции по неделям.

Вы разработаете скрипт, который анализирует еженедельные скачиваемые данные на активном листе. Он анализирует 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. Переименуйте скрипт в "Анализ веб-загрузки" и сохраните его.

Выполнение скрипта

Перейдите на любой из листов Week** и запустите скрипт Анализ веб-загрузок . Скрипт будет применять условное форматирование и метку расположения на текущем листе. Он также обновит лист сводки .

Перед выполнением скрипта

После выполнения скрипта