I am currently developing an Office Script that is designed to function within a single Excel worksheet. One challenge I am facing is the variability in whether or not these worksheets already contain tables. As a result, I have implemented a check to verify the existence of a table.
While I understand how to accomplish this with an if function and by creating a table within a sub-block if necessary, my struggle lies in referencing that table later on in the script to ensure its continuity. The issue arises from the fact that the table is declared within the main block, but if it needs to be created in a sub-block, the variables from the sub-block are not accessible outside of it.
Below is a snippet of the initial portion of the script where Line 10 and 23 encounter conflicts. Besides opting for a different variable name, I am seeking a solution to retrieve the table generated in the aforementioned sub-block.
/**
* This script aims to transform website and LinkedIn URLs into 'clean' domain names for matching purposes against other lists.
* @param worksheetName The name of the sheet containing the data you wish to process.
*/
function main(workbook: ExcelScript.Workbook,
worksheetName: string) {
// Obtain the table within this worksheet, assuming the first table is targeted.
const table = workbook.getWorksheet(worksheetName).getTables()[0];
if (!table){
// Retrieve the current worksheet.
const worksheet = workbook.getWorksheet(worksheetName);
const range = worksheet.getUsedRange();
// Generate a table with headers from the range and assign a name to the table.
const newTable = worksheet.addTable(range, true);
newTable.setName('tbl_datatoprocess');
}
// Access the table
const retrievedTable = workbook.getWorksheet(worksheetName).getTables()[0];
// The script progresses further from here...