Cenário de exemplo de Scripts do Office: Analisar transferências Web

Neste cenário, tem a tarefa de analisar os relatórios de transferência a partir do site da sua empresa. O objetivo desta análise é determinar se o tráfego da Web provém do Estados Unidos ou de outro lugar no mundo.

Os seus colegas carregam os dados não processados para o seu livro. O conjunto de dados de cada semana tem a sua própria folha de cálculo. Também existe a folha de cálculo Resumo com uma tabela e um gráfico que mostra as tendências semana a semana.

Irá desenvolver um script que analisa os dados de transferências semanais na folha de cálculo ativa. Analisará o endereço IP associado a cada transferência e determinará se veio ou não dos EUA. A resposta será inserida na folha de cálculo como um valor booleano ("VERDADEIRO" ou "FALSO") e a formatação condicional será aplicada a essas células. Os resultados da localização do endereço IP serão totais na folha de cálculo e copiados para a tabela de resumo.

Competências de scripting abrangidas

• Análise de texto
• Subfunções em scripts
• Formatação condicional
• Tabelas

Instruções de configuração

1. Transfira o livro de exemplo para o seu OneDrive.

   Transferir o livro de exemplo

2. Abra a pasta de trabalho no Excel.

3. No separador Automatizar , selecione Novo Script>Criar no Editor de Código. Cole o seguinte script no editor.

   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. Mude o nome do script para Analisar Transferências Web e guarde-o.

Executando o script

Navegue para qualquer uma das folhas de cálculo Semana** e execute o script Analisar Transferências Web . O script aplicará a formatação condicional e a etiquetagem de localização na folha atual. Também atualizará a folha de cálculo Resumo .

Antes de executar o script

Depois de executar o script