Append data using power automate and office scripts

Copper Contributor

Hi,

New to the forum, hopefully my post provides adequate information to assist and I'm not breaking any etiquette with my post.

Scenario:
We have some excel spreadsheets that come in every month. For each of these spreadsheets we have a matching "master" spreadsheet. Every month we append the data from the monthly spreadsheet into the master spreadsheet. The data is not in tables. 

The number of rows and columns in each spreadsheet is different. In each of the master spreadsheets there are some extra columns on the far right containing formulas that we want to copy down after appending the new monthly rows. There is a different number of extra columns on each master spreadsheet.

My solution so far:
Credit to https://exceloffthegrid.com/ for the scripts. I have only made very minor modifications.

I have an "export" script that reads the data in the monthly spreadsheet and returns the values and a count of the rows.

 

 

function main(workbook: ExcelScript.Workbook,
    wsName: string, startCell: string,
    headerRowsToRemove: number,
    footerRowsToRemove: number) {

    //Declare and assign the worksheet
    let ws = workbook.getWorksheet(wsName);

    //Delare and assign the data range
    let wsRng = ws.getRange(startCell).
        getSurroundingRegion()

    //Get the values of the data range in array
    let wsRngArr = wsRng.getValues();

    //Count the rows in array
    let wsRngArrRowCount = wsRngArr.length;

    //Calcualte the number of rows to return
    let wsRngArrRowLength = wsRngArrRowCount -
        headerRowsToRemove - footerRowsToRemove + 1;

    //Remove the top and bottom rows
    wsRngArr = wsRngArr.slice(headerRowsToRemove, wsRngArrRowLength);

    //Count the number of columns
    let columnCount = wsRng.getColumnCount()

    //Return the range
    return { wsRngArr, columnCount };

}

 

 

 

I have an "import" script that checks the number of rows in the master spreadsheet and sets a range that matches the number of rows from the master spreadsheet and the number of columns from the monthly spreadsheet (This range excludes the extra columns of formulas in the master spreadsheet).

 

Once I have that range I then collect the values. I concatenate the data from the monthly spreadsheet to those from the master spreadsheet. I then get a range matching the size of the newly combined dataset and set the values using the combined dataset.

 

 

function main(workbook: ExcelScript.Workbook, wsName: string,
    startCell: string, strArr: string, columnCount: number) {

    //Convert the strArr to an array
    let newDataArr: string[][] = JSON.parse(strArr)

    //Declare and assign the worksheet
    let ws = workbook.getWorksheet(wsName);

    //Get the old data
    let existingRng = ws.getRange(startCell).getSurroundingRegion();
    let rowCount = existingRng.getRowCount()
    existingRng = existingRng.getAbsoluteResizedRange(rowCount, columnCount)
    let dataArr = existingRng.getValues()

    //Join the dataArr and newDataArr
    dataArr = dataArr.concat(newDataArr)

    //Get the size of the range to paste to
    let dataRng = ws.getRange(startCell).
        getAbsoluteResizedRange(dataArr.length, dataArr[0].length)

    //Set the values of the new data set
    dataRng.setValues(dataArr)

}

 

 

I now have my monthly data appended to the bottom of my master spreadsheet.

What I need help with:
I still have to find a way to copy the extra columns of formulas down for the newly appended rows.

My thoughts are that I might be able to figure out how many columns of formulas there are (since its different for each master spreadsheet) by counting the total number of columns in the master spreadsheet and taking away the number of columns from the monthly data.  

I haven't copied formula's using office script before. If I just copy the values from one of the rows and use a loop to paste those values on the end of the newly appended rows, would that work?  If I copy the values for a formula, does it update the references for each row, or will they all reference the row they were copied from?

Summary:
If anyone can help me figure out the best way to deal with the extra columns of formulas, I'd be very grateful!

I also welcome any feedback on the export and import scripts. At the moment I'm only using small amounts of data for testing, but I'm aware that I will need to chunk/batch the export/import in my final version, as some of my monthly spreadsheets can be quite large with 10,000 rows (max) and up to 50 columns (max). So any tips to assistance with efficiency or performance is also appreciated!  

 

Many thanks! :smile:

2 Replies
Wouldn't it be simpler to format the data in the master sheets as table and let Excel handle the formula columns?
There are a bunch of other processes that use the data from the master spreadsheet. I would need to go and modify all of those to work with table data. Its definitely going to be easier to leave the data format of the master spreadsheet alone if possible.