question

RickyPinxt-9220 avatar image
0 Votes"
RickyPinxt-9220 asked YutaoHuang-MSFT answered

Append data to an excel file true office 365 scripts

I have 2 scripts that I want to use with Power Automate to append data from a (smaller) file to a (bigger) template file.
First comes the export script:
function main(workbook: ExcelScript.Workbook, sheetName: string, rangeAddress: string): string {
let sheet = workbook.getWorksheet(sheetName);
let range = sheet.getRange(rangeAddress);
return JSON.stringify(range.getValues());
}

And then the Import script:
function main(workbook: ExcelScript.Workbook, sheetName: string, rangeAddress: string, values: string) {
let sheet = workbook.getWorksheet(sheetName);
let range = sheet.getRange(rangeAddress);
range.setValues(JSON.parse(values));
}

Now in the automate flow I have to manually give the range for the export file and then also the range where to input it.
But of course this has to be variable.
182207-image.png

I guess something needs to change in the script as to Power Automate not to ask for a range??


office-scripts-excel-dev
image.png (50.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

YutaoHuang-MSFT avatar image
0 Votes"
YutaoHuang-MSFT answered

@RickyPinxt-9220 -

Can you elaborate on how the ranges to export from and to import into should be determined?

Do you want to export the content of all the used range from Export.xlsx and append them below the existing range in Import.xlsx?

If that is the case, you can use sheet.getUsedRange() to find out the used range (the range that contains data) in Export.xlsx. Here is the updated sample code:

function main(workbook: ExcelScript.Workbook, sheetName: string): string {
  let sheet = workbook.getWorksheet(sheetName);
  let range = sheet.getUsedRange();
  return JSON.stringify(range.getValues());
}


Then you can use range.getOffsetRange() and range.getAbsoluteResize() to determine where to import the data into in Import.xlsx. You will most likely need to know the number of rows and columns of the data transferred between the two workbooks. Here is the updated code:

function main(workbook: ExcelScript.Workbook, sheetName: string, values: string) {
  const sheet = workbook.getWorksheet(sheetName);
  const data: string[][] = JSON.parse(values);
  const numberOfRows = data.length;
  const numberOfColumns = data.length > 0 ? data[0].length : 0;
  if (numberOfRows > 0 && numberOfColumns > 0) {
    const usedRange = sheet.getUsedRange();
    const newRange = usedRange
      .getOffsetRange(usedRange.getRowCount(), 0)
      .getAbsoluteResizedRange(numberOfRows, numberOfColumns);
    newRange.setValues(data);
  }
}


By doing this, you won't need to specify the range addresses any more.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.