I have a report that I have to update frequently with new data so for a better efficiency, I need that my range is not static (for instance A2:A10000) but to be updated based on the last row after an update and the new rows of new data (for instance, the first day A2:A7, the second day A8:A20, the third day A21:A23, etc.). Once the specific word is found, the script will extract it in a new row because I need it to have its own one then delete any unnecessary spaces of the original row and sort the result. There are 2 options:

• Option 1: it will use the last row after finding the word by searching from down to top.
• Option 2: it will use the last row after finding the word by searching from top to down.

 

When I use the script ?

When I must extract particular words in a new single row for itself.

 

How to create the script ?

Read How to create, edit and select an Office Script in an excel report

 

How to create the button to associate it with the script ?

Read How to create a button and associated it to an Office Script in an excel report

 

How is/are the script(s) ?

Copy the code below and paste it into your script. You will see my comments in green if exist so follow the help to adapt to your need.

First, I need to create a start row cell like that:

The number 1 corresponds to the row 2 where I will put my data since row 1 (number 0) is the header and it is the starting number when I open the file the first time. This number will be updated after each update.

NOTE: in the picture, the “words (only informative)” columns are just to show you which words I am looking for.

Option 1:

function main(workbook: ExcelScript.Workbook) {
  // change sheet1 by yours
  let mainsheet = workbook.getWorksheet("Sheet1");
  // change G1 by yours
  let startrow = mainsheet.getRange("G1").getValue() as number; 
  let endword1 = getLastRow(mainsheet, mainsheet.getRange("A:A"));
  // change C by your word column to search
  let valword1 = mainsheet.getRange(`C${startrow + 1}:C${endword1 + 1}`).getValues();
  for (let iword1 = (valword1.length + startrow) - 1; iword1 >= startrow; iword1--) {
    // change 2 if not column C, i.e. 0 = column A, 1 = column B, etc. 
    let cellword1 = mainsheet.getCell(iword1, 2).getText().toLowerCase();
    // change nt by yours
    if (cellword1.includes("nt")) {
      let chaword1 = mainsheet.getCell(iword1, 2).getValue();
      let zonfword1 = mainsheet.getCell(iword1, 0).getAddress();
      // change 3 if not column D
      let zonlword1 = mainsheet.getCell(iword1, 3).getAddress();
      if (cellword1.includes("nt")) {
        // change 4 by the number of characters including before and after spaces
        // i.e NT has 2 characters + 1 space before + 1 space after
        if (chaword1.length > 4) {
          let zword1 = getLastRow(mainsheet, mainsheet.getRange("A:A"));
          mainsheet.getRange(`A${zword1 + 1}`).copyFrom(`${zonfword1}:${zonlword1}`);
          mainsheet.getCell(iword1, 2).setValue("NT");
          mainsheet.getRange(`C${zword1 + 1}`).replaceAll("nt", "", { completeMatch: false, matchCase: false });
          mainsheet.getRange(`C${zword1 + 1}`).replaceAll("  ", " ", { completeMatch: false, matchCase: false });
          let rngvword1 = mainsheet.getRange(`C${zword1 + 1}`).getValue() as string; 
          const trimword1 = rngvword1.trim();
          mainsheet.getRange(`C${zword1 + 1}`).setValue(trimword1); 
        }
      }
    }
  }
  let endword2 = getLastRow(mainsheet, mainsheet.getRange("A:A"));
  // change C by your word column to search  
  let valword2 = mainsheet.getRange(`C${startrow + 1}:C${endword2 + 1}`).getValues();
  for (let iword2 = (valword2.length + startrow) - 1; iword2 >= startrow; iword2--) {
    // change 2 if not column C, i.e. 0 = column A, 1 = column B, etc. 
    let cellword2 = mainsheet.getCell(iword2, 2).getText().toLowerCase();
    // change unix by yours    
    if (cellword2.includes("unix")) {
      let chaword2 = mainsheet.getCell(iword2, 2).getValue();
      let zonfword2 = mainsheet.getCell(iword2, 0).getAddress();
      // change 3 if not column D      
      let zonlword2 = mainsheet.getCell(iword2, 3).getAddress();
      if (cellword2.includes("unix")) {
        // change 6 by the number of characters including before and after spaces
        // i.e unix has 4 characters + 1 space before + 1 space after      
        if (chaword2.length > 6) {
          let zword2 = getLastRow(mainsheet, mainsheet.getRange("A:A"));
          mainsheet.getRange(`A${zword2 + 1}`).copyFrom(`${zonfword2}:${zonlword2}`);
          mainsheet.getCell(iword2, 2).setValue("unix");
          mainsheet.getRange(`C${zword2 + 1}`).replaceAll("unix", "", { completeMatch: false, matchCase: false });
          mainsheet.getRange(`C${zword2 + 1}`).replaceAll("  ", " ", { completeMatch: false, matchCase: false });
          let rngvword2 = mainsheet.getRange(`C${zword2 + 1}`).getValue() as string; 
          const trimword2 = rngvword2.trim();
          mainsheet.getRange(`C${zword2 + 1}`).setValue(trimword2); 
        }
      }
    }
  }
  let finalrow = getLastRow(mainsheet, mainsheet.getRange("A:A"));
  // change A2:D by yours
  // change 0 if not column A to sort
  // change true to false for descending
  mainsheet.getRange("A2:D" + finalrow).getSort().apply([{ key: 0, ascending: true }], false, false, ExcelScript.SortOrientation.rows); 
  mainsheet.getRange("G1").setValue(finalrow); 
}
// update mainsheet by yours
function getLastRow(mainsheet: ExcelScript.Worksheet, rng: ExcelScript.Range) {
  let lastCell1 = mainsheet.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());
  if (lastCell1.getValue().toString().length <= 0) { return lastCell1.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1; }
}              
              

Option 2:

function main(workbook: ExcelScript.Workbook) {
    // change sheet1 by yours
    let mainsheet = workbook.getWorksheet("Sheet1");
    // change G1 by yours
    let startrow = mainsheet.getRange("G1").getValue() as number; 
    let endword1 = getLastRow(mainsheet, mainsheet.getRange("A:A"));
    // change C by your word column to search
    let valword1 = mainsheet.getRange(`C${startrow + 1}:C${endword1 + 1}`).getRowCount();
    for (let iword1 = startrow; iword1 <= valword1 + startrow; iword1++) {
        // change 2 if not column C, i.e. 0 = column A, 1 = column B, etc. 
        let cellword1 = mainsheet.getCell(iword1, 2).getText().toLowerCase();
        // change nt by yours
        if (cellword1.includes("nt")) {
            let chaword1 = mainsheet.getCell(iword1, 2).getValue();
            let zonfword1 = mainsheet.getCell(iword1, 0).getAddress();
            // change 3 if not column D
            let zonlword1 = mainsheet.getCell(iword1, 3).getAddress();
            if (cellword1.includes("nt")) {
                // change 4 by the number of characters including before and after spaces
                // i.e NT has 2 characters + 1 space before + 1 space after
                if (chaword1.length > 4) {
                    let zword1 = getLastRow(mainsheet, mainsheet.getRange("A:A"));
                    mainsheet.getRange(`A${zword1 + 1}`).copyFrom(`${zonfword1}:${zonlword1}`);
                    mainsheet.getCell(iword1, 2).setValue("NT");
                    mainsheet.getRange(`C${zword1 + 1}`).replaceAll("nt", "", { completeMatch: false, matchCase: false });
                    mainsheet.getRange(`C${zword1 + 1}`).replaceAll("  ", " ", { completeMatch: false, matchCase: false });
                    let rngvword1 = mainsheet.getRange(`C${zword1 + 1}`).getValue() as string; 
                    const trimword1 = rngvword1.trim();
                    mainsheet.getRange(`C${zword1 + 1}`).setValue(trimword1); 
                }
            }
        }
    }
    let endword2 = getLastRow(mainsheet, mainsheet.getRange("A:A"));
    // change C by your word column to search    
    let valword2 = mainsheet.getRange(`C${startrow + 1}:C${endword2 + 1}`).getRowCount();
    for (let iword2 = startrow; iword2 <= valword2 + startrow; iword2++) {
        // change 2 if not column C, i.e. 0 = column A, 1 = column B, etc.     
        let cellword2 = mainsheet.getCell(iword2, 2).getText().toLowerCase();
        // change unix by yours        
        if (cellword2.includes("unix")) {
            let chaword2 = mainsheet.getCell(iword2, 2).getValue();
            let zonfword2 = mainsheet.getCell(iword2, 0).getAddress();
            // change 3 if not column D            
            let zonlword2 = mainsheet.getCell(iword2, 3).getAddress();
            if (cellword2.includes("unix")) {
                // change 6 by the number of characters including before and after spaces
                // i.e unix has 4 characters + 1 space before + 1 space after            
                if (chaword2.length > 6) {
                    let zword2 = getLastRow(mainsheet, mainsheet.getRange("A:A"));
                    mainsheet.getRange(`A${zword2 + 1}`).copyFrom(`${zonfword2}:${zonlword2}`);
                    mainsheet.getCell(iword2, 2).setValue("unix");
                    mainsheet.getRange(`C${zword2 + 1}`).replaceAll("unix", "", { completeMatch: false, matchCase: false });
                    mainsheet.getRange(`C${zword2 + 1}`).replaceAll("  ", " ", { completeMatch: false, matchCase: false });
                    let rngvword2 = mainsheet.getRange(`C${zword2 + 1}`).getValue() as string; 
                    const trimword2 = rngvword2.trim();
                    mainsheet.getRange(`C${zword2 + 1}`).setValue(trimword2); 
                }
            }
        }
    }
    let finalrow = getLastRow(mainsheet, mainsheet.getRange("A:A"));
    // change A2:D by yours
    // change 0 if not column A to sort
    // change true to false for descending
    mainsheet.getRange("A2:D" + finalrow).getSort().apply([{ key: 0, ascending: true }], false, false, ExcelScript.SortOrientation.rows); 
    mainsheet.getRange("G1").setValue(finalrow); 
}
// update mainsheet by yours
function getLastRow(mainsheet: ExcelScript.Worksheet, rng: ExcelScript.Range) {
  let lastCell = mainsheet.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());
  if (lastCell.getValue().toString().length <= 0) { return lastCell.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1; }
}