Table empty examples

Question

I followed the guide LINK

The last part references "One thing to note is that the second script doesn't delete old range values from previous runs. This can be done in a number of different ways. But the preferred way may depend on how the workbook is structured. So I'd recommend writing code to clear the range in the second script somewhere in the beginning. Or better yet, add the output of the first script into an Excel table. And just empty out the table every time you run the second script."

Can an example be made expanding on this please! Thank you.

Answer 1

For reference, this is the second script:

function main(workbook: ExcelScript.Workbook, tableValues: string)
{
  let sh: ExcelScript.Worksheet = workbook.getWorksheet("Sheet1");
  //parses the JSON string to create array
  let tableValuesArray: string[][] = JSON.parse(tableValues);
  //gets row count from the array
  let valuesRowCount: number = tableValuesArray.length - 1;
  //gets column count from the array
  let valuesColumnCount: number = tableValuesArray[0].length - 1;
  //resizes the range
  let rang: ExcelScript.Range = sh.getRange("A1").getResizedRange(valuesRowCount,valuesColumnCount);
  //sets the value of the resized range to the array
  rang.setValues(tableValuesArray);
}

In this script, no data is deleted. This is not ideal, but is fine if the data set has at least as much data as was previously in the range. If it has less data, then you'll get a mix of old data and new data.

How to deal with the data depends on how the worksheet in the destination range is structured. In the initial script, Script 1, three columns are selected (e.g. A:C). So for simplicity purposes, let's assume that these three columns only have data from this pasted script. To clear the non-header data, assuming the headers are on row 1, you could start at A2 and concatenate the rows for the used range to column C to get the non-header range:

function main(workbook: ExcelScript.Workbook) {
  let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet();
  let usedRange: ExcelScript.Range = sh.getUsedRange();
  let rang: ExcelScript.Range = sh.getRange("A2:C" + usedRange.getRowCount());
  rang.clear();
}

So if this script was added to Script 2 in the beginning, it would clear out the contents of the range each time the flow was run.

As I noted in the initial post, it would be easier to use an Excel table. If you did that, you could work with the table's dataBodyRange rather than trying to determine it dynamically. You can work with the dataBodyRange using the getRangeBetweenHeaderAndTotal() method. So if the destination range was in an Excel table, you could just write code like this:

function main(workbook: ExcelScript.Workbook) {
  let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet();
  let tbl: ExcelScript.Table = sh.getTable("Table1");
  tbl.getRangeBetweenHeaderAndTotal().delete(ExcelScript.DeleteShiftDirection.up);
}

This is a better design imo. It allows you to work with the exact range in the table. With the first example, your table could have a small range, but your sheet could have a large used range. So you could potentially be clearing an unnecessarily large used range. But that doesn't happen with this second example. The first example would also need to be updated if the header row was not in row 1 for example. If it was in row2, the rang variable would need to start with A3 for example.

If you want to work with the table in this way, you'd have to update the second script to add rows dynamically to the table depending on the number of rows passed from the first script. You can use tableValues to do this. So you'd have to modify script 2 with a new line like this to add the values from the first script to the table:

  tbl.addRows(0, tableValues)