Update Script to AddColumn and Enter Text

[Phishdawg]

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 data, the word "Continuous".

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");

}

[#2]

Hi @Phishdawg,

To update your script to add a column and enter text, you can use the following code:

function main(workbook: ExcelScript.Workbook) {
    // Get the worksheet named "Continuous - New & Changes"
    let selectedSheet = workbook.getWorksheet("Continuous - New & Changes");

    // Set the value "EmployeeID" in cell B8
    selectedSheet.getRange("B8").setValue("EmployeeID");

    // Delete rows 1 to 7 on the selected sheet
    selectedSheet.getRange("1:7").delete(ExcelScript.DeleteShiftDirection.up);

    // Set the date format for ranges I:Q, X:X, AG:AH on the selected sheet
    selectedSheet.getRange("I:Q").setNumberFormatLocal("m/d/yyyy");
    selectedSheet.getRange("X:X").setNumberFormatLocal("m/d/yyyy");
    selectedSheet.getRange("AG:AH").setNumberFormatLocal("m/d/yyyy");

    // Create a table with the format in the range A1:AP25 on the selected sheet
    let Continuous_Tbl = workbook.addTable(selectedSheet.getRange("A1:AP25"), true);
    Continuous_Tbl.setName("Continuous_Tbl");
    Continuous_Tbl.setPredefinedTableStyle("TableStyleLight8");

    // Add a new column titled "UpdateType" and set the value "Continuous" for every data row
    let updateTypeColumn = Continuous_Tbl.addColumn();
    updateTypeColumn.getNameRange().values = "UpdateType";  // Set the column title
    updateTypeColumn.getDataBodyRange().setValue("Continuous");  // Set "Continuous" for all data rows
}

1. script starts by obtaining the worksheet named "Continuous - New & Changes" within the Excel workbook.

2. the it sets the value "EmployeeID" in cell B8 on the selected sheet.

3. Rows 1 to 7 on the selected sheet are deleted using delete(ExcelScript.DeleteShiftDirection.up).

4.  specific ranges (I:Q, X:X, AG:AH) are formatted as dates with the "m/d/yyyy" format.

5. A new table is created on the selected sheet, spanning from A1 to AP25, with a predefined style.

6. A new column called "UpdateType" is added to the table, and the title is set in the header row.

7. The value "Continuous" is set in the "UpdateType" column for all data rows in the table.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

[#3]

The following -
   .getNameRange().
   .getDataBodyRange().

are kicking back the following error -
   Property 'getNameRange' does not exist on type 'TableColumn'. (2339)
   Property 'getDataBodyRange' does not exist on type 'TableColumn'. (2339)

let updateTypeColumn = Continuous_Tbl.addColumn();
   updateTypeColumn.getNameRange().values = "UpdateType";
   updateTypeColumn.getDataBodyRange().setValue("Continuous");

[#4 · Accepted solution]

@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")

}

[#5]

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!!

[#6]

@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.