Confused about declaring and re-declaring variables

Issue

I'm writing an Office Script that works in a single Excel worksheet. Because some worksheets may or may not already have a table to them, I want to start by checking the existence of a table.

I know how to do that with an if function and then using a sub-block to create the table if needed. I'm struggling is that I need refer that table later again my script to continue the script. This because the table is declared within the same main block but if not existing, is is created in a sub-block and variables form this sub-block are not usable outside of it.

Bellow is the first part of the script. Line 10 and 23 are clashing. Aside from using another variable name, how can I still get the table that is being created in the sub-block above?

/**
 * This script will convert website and Linkedin URLs into 'clean' domain names to be used later on for matching purpose againts other lists.
 * @param worksheetName The name of the sheet which contain the data you want to process.
 */

function main(workbook: ExcelScript.Workbook,
worksheetName: string) {

  // Get the table in this worksheet, we asume the first table is the one.
  const table = workbook.getWorksheet(worksheetName).getTables()[0];
  if (!table){
    
    // Get the current worksheet.
    const worksheet = workbook.getWorksheet(worksheetName);
    const range = worksheet.getUsedRange();

    // Create a table that has headers from that range and set the table name.
    const table = worksheet.addTable(range, true);
    table.setName('tbl_datatoprocess');
  }
    
  // Get the table
  const table = workbook.getWorksheet(worksheetName).getTables()[0];

// And the script continues under this...

Solution

There is a difference between declaring a variable (equals creating a variable (and assigning a value)) and "just" assigning a value.

You only declare a variable once if you do it in an if/loop etc, you can use it only in that. But if you declare it outside an if/loop etc, you can still use it inside it.

Also, const (as in constant) variables can not change value. You declare them once, and then they stay as they are. If you want to assign different values, you should use let in the declaration. When assigning a value, you leave out const/letand you write the variable's name.

/**
 * This script will convert website and Linkedin URLs into 'clean' domain names to be used later on for matching purpose againts other lists.
 * @param worksheetName The name of the sheet which contain the data you want to process.
 */

function main(workbook: ExcelScript.Workbook,
worksheetName: string) {

  // Inti table with your assumed value
  let table = workbook.getWorksheet(worksheetName).getTables()[0];
  if (!table){
    
    // Get the current worksheet.
    const worksheet = workbook.getWorksheet(worksheetName);
    const range = worksheet.getUsedRange();

    // Assigning another value happens only if the first assignment in the declaration leads you into the IF-statement
    table = worksheet.addTable(range, true);
    table.setName('tbl_datatoprocess');
  }
    

// And the script continues under this...

You can replace the whole if-section in one line (well three):

function main(workbook: ExcelScript.Workbook,
worksheetName: string) {

// we get the worksheet name where we are in case we need it
const worksheet = workbook.getWorksheet(worksheetName);
const range = worksheet.getUsedRange();
// We check if the table exist (aka is not NULL), if so we assign it to our table. If it does not exist we use the worksheet and range to create a new one and assign it.
// taget variable = condition ? do-if-true : do-if-false
const table = workbook.getWorksheet(worksheetName).getTables()[0] !== NULL ? workbook.getWorksheet(worksheetName).getTables()[0] : worksheet.addTable(worksheet,range);

Answered By - A-Tech