Excel Macro Error Log : Line 5: Cannot read properties of undefined (reading 'getRangeBetweenHeaderAndTotal')

Question

I recorded a small macro where I take a table that was loaded from Power Query and format it to where there are not empty cells and I have the information I need. All I did was copy and past values from different cells into other cells and then made some format changes at the end like borders, font size, and etc.

It worked for my first tab but when I tried to run the macro for the next tab of unorganized data, I got that error message.

This is the line of code in question:

selectedSheet.getRange("A1").copyFrom(table_INV_1020845.getColumn("Column1").getRangeBetweenHeaderAndTotal().getRow(0), ExcelScript.RangeCopyType.all, false, false);

I tried the advice from this post to no avail. I need this macro to work for hundreds of other tabs of data that is exactly identical except for cell values.

Adding the complete code for clarity:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getWorksheets()[0];
    let table_INV_1020845 = workbook.getTable("Table_INV_1020845");
    // Paste to range A1 on selectedSheet from table table_INV_1020845 cell in row 0 on column Column1
    selectedSheet.getRange("A1").copyFrom(table_INV_1020845.getColumns()[0].getRangeBetweenHeaderAndTotal().getRow(0), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 0 on column STYLE from table table_INV_1020845 cell in row 1 on column STYLE
    table_INV_1020845.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(table_INV_1020845.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(1), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 1 on column STYLE from table table_INV_1020845 cell in row 5 on column STYLE
    table_INV_1020845.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(1).copyFrom(table_INV_1020845.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(5), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 2 on column STYLE from table table_INV_1020845 cell in row 10 on column STYLE
    table_INV_1020845.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(2).copyFrom(table_INV_1020845.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(10), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 3 on column STYLE from table table_INV_1020845 cell in row 20 on column ^{Invoice #} 1020845
    table_INV_1020845.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(3).copyFrom(table_INV_1020845.getColumn("^{Invoice #} 1020845").getRangeBetweenHeaderAndTotal().getRow(20), ExcelScript.RangeCopyType.all, false, false);
    // Set range A5 on selectedSheet
    selectedSheet.getRange("A5").setValue("TOTAL");
    // Paste to range B1 on selectedSheet from table table_INV_1020845 cell in row 0 on column Column13
    selectedSheet.getRange("B1").copyFrom(table_INV_1020845.getColumn("Column13").getRangeBetweenHeaderAndTotal().getRow(0), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 0 on column EA from table table_INV_1020845 cell in row 1 on column Column13
    table_INV_1020845.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(table_INV_1020845.getColumn("Column13").getRangeBetweenHeaderAndTotal().getRow(1), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 1 on column EA from table table_INV_1020845 cell in row 5 on column Column13
    table_INV_1020845.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(1).copyFrom(table_INV_1020845.getColumn("Column13").getRangeBetweenHeaderAndTotal().getRow(5), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 2 on column EA from table table_INV_1020845 cell in row 10 on column Column13
    table_INV_1020845.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(2).copyFrom(table_INV_1020845.getColumn("Column13").getRangeBetweenHeaderAndTotal().getRow(10), ExcelScript.RangeCopyType.all, false, false);
    // Paste to range C1 on selectedSheet from table table_INV_1020845 cell in row 0 on column Column12
    selectedSheet.getRange("C1").copyFrom(table_INV_1020845.getColumn("Column12").getRangeBetweenHeaderAndTotal().getRow(0), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 0 on column QTY from table table_INV_1020845 cell in row 1 on column Column12
    table_INV_1020845.getColumn("QTY").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(table_INV_1020845.getColumn("Column12").getRangeBetweenHeaderAndTotal().getRow(1), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 1 on column QTY from table table_INV_1020845 cell in row 5 on column Column12
    table_INV_1020845.getColumn("QTY").getRangeBetweenHeaderAndTotal().getRow(1).copyFrom(table_INV_1020845.getColumn("Column12").getRangeBetweenHeaderAndTotal().getRow(5), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 2 on column QTY from table table_INV_1020845 cell in row 10 on column Column12
    table_INV_1020845.getColumn("QTY").getRangeBetweenHeaderAndTotal().getRow(2).copyFrom(table_INV_1020845.getColumn("Column12").getRangeBetweenHeaderAndTotal().getRow(10), ExcelScript.RangeCopyType.all, false, false);
    // Set range A1 on selectedSheet
    selectedSheet.getRange("A1").setValue("^{Invoice #} 1020845");
    // Set range D1:D5 on selectedSheet
    selectedSheet.getRange("D1:D5").setValues([["Invoice #"],["-"],["-"],["-"],[1020845]]);
    // Paste to table table_INV_1020845 cell in row 3 on column QTY from table table_INV_1020845 cell in row 20 on column EA
    table_INV_1020845.getColumn("QTY").getRangeBetweenHeaderAndTotal().getRow(3).copyFrom(table_INV_1020845.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(20), ExcelScript.RangeCopyType.all, false, false);
    // Paste to table table_INV_1020845 cell in row 3 on column EA from table table_INV_1020845 cell in row 20 on column Column5
    table_INV_1020845.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(3).copyFrom(table_INV_1020845.getColumn("Column5").getRangeBetweenHeaderAndTotal().getRow(20), ExcelScript.RangeCopyType.all, false, false);
    // Delete range E:O on selectedSheet
    selectedSheet.getRange("E:O").delete(ExcelScript.DeleteShiftDirection.left);
    // Delete range 6:23 on selectedSheet
    selectedSheet.getRange("6:23").delete(ExcelScript.DeleteShiftDirection.up);
    // Set font size to 12 for all cells on selectedSheet
    selectedSheet.getRange().getFormat().getFont().setSize(12);
    // Set horizontal alignment to ExcelScript.HorizontalAlignment.center for all cells on selectedSheet
    selectedSheet.getRange().getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
    selectedSheet.getRange().getFormat().setIndentLevel(0);
    // Set vertical alignment to ExcelScript.VerticalAlignment.bottom for all cells on selectedSheet
    selectedSheet.getRange().getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.bottom);
    selectedSheet.getRange().getFormat().setIndentLevel(0);
    // Set wrap text to false for all cells on selectedSheet
    selectedSheet.getRange().getFormat().setWrapText(false);
    // Set text orientation to 0 for all cells on selectedSheet
    selectedSheet.getRange().getFormat().setTextOrientation(0);
    // Indent set to 0 for all cells on selectedSheet
    selectedSheet.getRange().getFormat().setIndentLevel(0);
    // Set width of column(s) at all cells on selectedSheet to 10.4
    selectedSheet.getRange().getFormat().setColumnWidth(10.4);
    // Auto fit the columns of all cells on selectedSheet
    selectedSheet.getRange().getFormat().autofitColumns();
    // Set font bold to true for 1 row(s) at index 3 on table table_INV_1020845
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getFont().setBold(true);
    // Set border for 1 row(s) at index 3 on table table_INV_1020845
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.diagonalDown).setStyle(ExcelScript.BorderLineStyle.none);
    // Set border for 1 row(s) at index 3 on table table_INV_1020845
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.diagonalUp).setStyle(ExcelScript.BorderLineStyle.none);
    // Set border for 1 row(s) at index 3 on table table_INV_1020845
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setStyle(ExcelScript.BorderLineStyle.continuous);
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeLeft).setWeight(ExcelScript.BorderWeight.medium);
    // Set border for 1 row(s) at index 3 on table table_INV_1020845
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setStyle(ExcelScript.BorderLineStyle.continuous);
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeTop).setWeight(ExcelScript.BorderWeight.medium);
    // Set border for 1 row(s) at index 3 on table table_INV_1020845
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setStyle(ExcelScript.BorderLineStyle.continuous);
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeBottom).setWeight(ExcelScript.BorderWeight.medium);
    // Set border for 1 row(s) at index 3 on table table_INV_1020845
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setStyle(ExcelScript.BorderLineStyle.continuous);
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.edgeRight).setWeight(ExcelScript.BorderWeight.medium);
    // Set border for 1 row(s) at index 3 on table table_INV_1020845
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideVertical).setStyle(ExcelScript.BorderLineStyle.none);
    // Set border for 1 row(s) at index 3 on table table_INV_1020845
    table_INV_1020845.getRangeBetweenHeaderAndTotal().getRow(3).getFormat().getRangeBorder(ExcelScript.BorderIndex.insideHorizontal).setStyle(ExcelScript.BorderLineStyle.none);
}

I have also added screenshots showing what the output should be and another screenshot of what the table looks like before running the macro so you can see which cells I am trying to change and what the format of the other invoices will look like.

The table before editing anything. I only need a few pieces of information from this table, and the hundreds of others that have this exact look, which is why I recorded the macro.

Desired output.

taller_ExcelHome · Answer 1 · 2023-08-12T04:58:39.690

Most of code appears to be generated by script recording. There are more efficient approaches to accomplish this task.

Side note: The invoice number in the last cell are automatically extracted from the table name (ie. the last part of Table_INV_1020845). Update the logic if your approach differs.

function main(workbook: ExcelScript.Workbook) {
  // restore(workbook);
  // Get the frist worksheet
  // let selectedSheet = workbook.getWorksheets()[0];
  // Get the Active worksheet
  let selectedSheet = workbook.getActiveWorksheet();
  let tableName = "Table_INV_1020845";
  let table_INV_1020845 = selectedSheet.getTable(tableName);
  let tabValue = table_INV_1020845.getRange().getTexts();
  let resultValue = [] = [];
  let rowList = [1, 2, 6, 11]
  rowList.forEach( row => {
    let invoiceVal = "-";
    if(row === 1) { invoiceVal = 'Invoice #' };
    let rowValue = [tabValue[row][0],tabValue[row][12],tabValue[row][11], invoiceVal];
    resultValue.push(rowValue);
  })
  let rowValue = ["TOTAL", tabValue[21][4], tabValue[21][1], tableName.split("_")[2] ];
  resultValue.push(rowValue);
  selectedSheet.getRange("A1:D5").setValues(resultValue);
  // console.log(resultValue)];
  // Resize table
  table_INV_1020845.resize(selectedSheet.getRange("A1:D5"));
  // Delete range E:O on selectedSheet
  selectedSheet.getRange("E:O").clear(ExcelScript.ClearApplyTo.all);
  // Delete range 6:23 on selectedSheet
  selectedSheet.getRange("6:23").clear(ExcelScript.ClearApplyTo.all);
  let tabFormat = table_INV_1020845.getRange().getFormat();
  // Set font size to 12 for all cells on selectedSheet
  tabFormat.getFont().setSize(12);
  // Set horizontal alignment to ExcelScript.HorizontalAlignment.center for all cells on selectedSheet
  tabFormat.setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
  tabFormat.setIndentLevel(0);
  // Set vertical alignment to ExcelScript.VerticalAlignment.bottom for all cells on selectedSheet
  tabFormat.setVerticalAlignment(ExcelScript.VerticalAlignment.bottom);
  tabFormat.setIndentLevel(0);
  // Set wrap text to false for all cells on selectedSheet
  tabFormat.setWrapText(false);
  // Set text orientation to 0 for all cells on selectedSheet
  tabFormat.setTextOrientation(0);
  // Indent set to 0 for all cells on selectedSheet
  tabFormat.setIndentLevel(0);
  // Set width of column(s) at all cells on selectedSheet to 10.4
  tabFormat.setColumnWidth(10.4);
  // Auto fit the columns of all cells on selectedSheet
  tabFormat.autofitColumns();
  tabFormat = table_INV_1020845.getRange().getRow(4).getFormat();
  // Set font bold to true for 1 row(s) at index 3 on table table_INV_1020845
  tabFormat.getFont().setBold(true);
  // Set border
  let listBorder0 = [ExcelScript.BorderIndex.diagonalDown, ExcelScript.BorderIndex.diagonalUp,
    ExcelScript.BorderIndex.insideVertical, ExcelScript.BorderIndex.insideHorizontal];
  let listBorder1 = [ExcelScript.BorderIndex.edgeTop, ExcelScript.BorderIndex.edgeLeft,
    ExcelScript.BorderIndex.edgeRight, ExcelScript.BorderIndex.edgeBottom];
  for(let border of listBorder0){
    tabFormat.getRangeBorder(border).setStyle(ExcelScript.BorderLineStyle.none);
  }        
  for(let border of listBorder1){
    tabFormat.getRangeBorder(border).setStyle(ExcelScript.BorderLineStyle.continuous);
    tabFormat.getRangeBorder(border).setWeight(ExcelScript.BorderWeight.medium);
  }
  table_INV_1020845.getColumn("EA").getRangeBetweenHeaderAndTotal().getRow(3).setNumberFormatLocal("0.00");
}

getColumn("Column1") gets the column in a table by name. Please verify that the header row of the table table_INV_1020845 contains a column labeled "Column1" (that is, the text in a cell is "Column1").

If you only need to refers the first column in a table, you can use following code.

  selectedSheet.getRange("A1").copyFrom(table_INV_1020845.getColumns()[0].getRangeBetweenHeaderAndTotal().getRow(0), ExcelScript.RangeCopyType.all, false, false);

btw, If you want to copy the FIRST cell in the FIRST column of data range, the code could be simpler.

 selectedSheet.getRange("A1").copyFrom(table_INV_1020845.getRangeBetweenHeaderAndTotal().getCell(0, 0), ExcelScript.RangeCopyType.all, false, false);

I made the changes you suggested in the first paragraph and ran into another problem in line 9 that reads as "Line 9: Range getRow: Parameter out of range". The code is - table_INV_1020845.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(1).copyFrom(table_INV_1020845.getColumn("STYLE").getRangeBetweenHeaderAndTotal().getRow(5), ExcelScript.RangeCopyType.all, false, false); — lostexcelkid, Aug 11 '23 at 14:17
What is confusing me is why there as many issues? Each invoice I am trying to reformat has all of the data in the same exact cells. If I record a macro copy and pasting something that always appears in cell A2 and pasting in cell A1, this is the "STYLE" column, why am I getting issues when trying to repeat this for other sheets? — lostexcelkid, Aug 11 '23 at 14:19
Including all your code in OP is helpful for troubleshooting. If you want to copy A2 to A1, the option 2 in my reply is good enough. — taller_ExcelHome, Aug 11 '23 at 14:44
"Line 9: Range getRow: Parameter out of range" - Typically, it comes when there isn't enough data (rows) in the table. `.getRow(5)` will fetch the 6th row from table (header row dosn't count). — taller_ExcelHome, Aug 11 '23 at 14:46
I have added the complete code and screenshots to maybe make it clearer what I am trying to do and maybe even see if what I am doing is efficient or if their is a better way — lostexcelkid, Aug 11 '23 at 17:27
Thank you so very much for trying to help me but your code also runs into errors. At this point, I will just accept I need to do this manually as I unfortunately do not have more time to find an easier solution? Thank you again! — lostexcelkid, Aug 14 '23 at 13:49
Show the detail of errors in OP. It is helpful for troubleshooting. It should work well if my ouput screenshot is correct. — taller_ExcelHome, Aug 14 '23 at 14:37

Excel Macro Error Log : Line 5: Cannot read properties of undefined (reading 'getRangeBetweenHeaderAndTotal')

1 Answers1