Forum Discussion

Brass Contributor

Oct 18, 2023

Update Script to AddColumn and Enter Text

I am using the following script to update a sheet in an Excel workbook. I also have the need to initiate AddColumn (Column Title: UpdateType), and add to that column - on every line where there is...

Office Scripts

SergeiBaklan

Oct 19, 2023

Phishdawg

You may continue the scripts as

function main(workbook: ExcelScript.Workbook) {

// =======
  let selectedSheet = workbook.getWorksheet("Continuous - New & Changes");
  // Set range B1 on selectedSheet
  selectedSheet.getRange("B8").
    setValue("EmployeeID");
  // Delete range 1:7 on selectedSheet
  selectedSheet.getRange("1:7").
    delete(ExcelScript.DeleteShiftDirection.up);
  // Set number format for range I:Q on selectedSheet
  selectedSheet.getRange("I:Q").
    setNumberFormatLocal("m/d/yyyy");
  // Set number format for range X:X on selectedSheet
  selectedSheet.getRange("X:X").
    setNumberFormatLocal("m/d/yyyy");
  // Set number format for range AG:AH on selectedSheet
  selectedSheet.getRange("AG:AH").
    setNumberFormatLocal("m/d/yyyy");
  // Create a table with format on range A1:AP25 on selectedSheet
  let Continuous_Tbl = workbook.addTable(selectedSheet.getRange("A1:AP25"), true);
  Continuous_Tbl.setName("Continuous_Tbl");
  Continuous_Tbl.setPredefinedTableStyle("TableStyleLight8");
//========

  const values = Continuous_Tbl
    .getRangeBetweenHeaderAndTotal()
    .getValues()
  let col: string[] = []

  col.push("")
  for (let row of values) {
    let emptyRow = true;

    for (let cell of row) {
      if (cell.toString().length > 0) {
        emptyRow = false
      }
    }
    col.push(emptyRow ? "" : "Continuous" )
  }

  Continuous_Tbl.addColumn(-1, col, "UpdateType")

}

SergeiBaklan

MVP

Phishdawg

You may continue the scripts as

function main(workbook: ExcelScript.Workbook) {

// =======
  let selectedSheet = workbook.getWorksheet("Continuous - New & Changes");
  // Set range B1 on selectedSheet
  selectedSheet.getRange("B8").
    setValue("EmployeeID");
  // Delete range 1:7 on selectedSheet
  selectedSheet.getRange("1:7").
    delete(ExcelScript.DeleteShiftDirection.up);
  // Set number format for range I:Q on selectedSheet
  selectedSheet.getRange("I:Q").
    setNumberFormatLocal("m/d/yyyy");
  // Set number format for range X:X on selectedSheet
  selectedSheet.getRange("X:X").
    setNumberFormatLocal("m/d/yyyy");
  // Set number format for range AG:AH on selectedSheet
  selectedSheet.getRange("AG:AH").
    setNumberFormatLocal("m/d/yyyy");
  // Create a table with format on range A1:AP25 on selectedSheet
  let Continuous_Tbl = workbook.addTable(selectedSheet.getRange("A1:AP25"), true);
  Continuous_Tbl.setName("Continuous_Tbl");
  Continuous_Tbl.setPredefinedTableStyle("TableStyleLight8");
//========

  const values = Continuous_Tbl
    .getRangeBetweenHeaderAndTotal()
    .getValues()
  let col: string[] = []

  col.push("")
  for (let row of values) {
    let emptyRow = true;

    for (let cell of row) {
      if (cell.toString().length > 0) {
        emptyRow = false
      }
    }
    col.push(emptyRow ? "" : "Continuous" )
  }

  Continuous_Tbl.addColumn(-1, col, "UpdateType")

}

Phishdawg

Brass Contributor

Oct 19, 2023

Thank you!

Not to be a pain but, as your time allows, can you provide comments to each of the commands in your edit?

It will help understand and learn.

Thank You!!

SergeiBaklan
MVP
Oct 20, 2023
Phishdawg

We get all values (2D array) from the created table and initiate empty array of string type (1D array).

Push method adds another value to such array.

Next we iterate values array by row within initial value indicates it is empty (

let emptyRow = true;

and next iterate each such row by cells. If at least one cell in the rows is not empty we change emptyRow on false. Above is based on that sample Count blank rows on sheets - Office Scripts | Microsoft Learn

Depends on row is empty or not we add another value to string array using short form of if/then/else

col.push(emptyRow ? "" : "Continuous" )

Finally we add new column to the table using this ExcelScript.Table interface - Office Scripts | Microsoft Learn method with values from the generated string array.