Forum Discussion

Phishdawg's avatar
Phishdawg
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 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");
}
  • 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")
    
    }
  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor

    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)



    • Phishdawg's avatar
      Phishdawg
      Brass Contributor

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

      • SergeiBaklan's avatar
        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")
        
        }

Resources