Append data to an excel file true office 365 scripts

Question

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.

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

Answer

@Ricky Pinxt -

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.

Append data to an excel file true office 365 scripts

1 answer