How to pass values from selected columns of Workbook1 via Json in scrip1 to script2 so update Workbook2 using Power Automate

Question

I have a long Excel table in workbook1 with many columns. I only want to select values from the first 5 columns of the workbook1 in Office Script and pass these values via JSON to 2nd script for processing. The scrip1 and script2 will be running in Power Automate.

I have found a sample script on the web, but it passes all the values of the Excel table as a JSON file for 2nd script to processing. But I only want to pass the values of the first 5 columns. How to achieve it?

The sample script is as below:-

function main(workbook: ExcelScript.Workbook): string {
    // Get work sheet
    let dailySht = workbook.getWorksheet("Sheet1");
    // Get table
    let dailyTab = dailySht.getTables()[0].getRangeBetweenHeaderAndTotal();
    // Load data
    let dailyValue = dailyTab.getTexts();
    let dailyJSON = JSON.stringify(dailyValue);
    return dailyJSON;
}

Answer 1

• Your code is close to finish. Use getAbsoluteResizedRange to resize the source data range.

Microsoft documentation:

getAbsoluteResizedRange(numRows, numColumns)

function main(workbook: ExcelScript.Workbook): string {
  // Get work sheet
  let dailySht = workbook.getWorksheet("Sheet1");
  // Get table
  let dailyTab = dailySht.getTables()[0].getRangeBetweenHeaderAndTotal();
  const colCnt = 5;
  let selectedTab = dailyTab.getAbsoluteResizedRange(dailyTab.getRowCount(), colCnt);
  // Load data
  let dailyValue = selectedTab.getTexts();
  let dailyJSON = JSON.stringify(dailyValue);
  // console.log(dailyJSON);
  return dailyJSON;
}