Copy data from one workbook to another in Excel Online

Question

I am using Excel Online in the browser, have setup a workbook link to my main file from a source. In my main file I have table headers and additional columns with formula. I just need from A2 to AC down. The issue is that the source file changes daily. There might be more rows the next day or fewer. I need to be able to reference set columns and then detect how many rows are in the data source and update the main file

So far, I have something like this

='https://sharepoint.com/personal/myFolder/Documents/[data_source.xlsx]in'!A2

Which on columns B2 and C2 load the first row. I can select a range from the source data so it loads all of it, but if the next day there is more rows, it wont load those, or if there are fewer, it will display as blanks.

How can I tell the formula to select Columns A2 to C2 and extend down, or refresh the data like it does in Excel desktop when using data connections?

As you can see Source data, Day 2 has extra rows that wont be loaded in my main file.

Answer 1

You can use PowerAutomate and two Office Scripts to link the two workbooks together.

You'd start by using a recurrence. So you'd pick how often you'd like the flow to run (weekly, daily, etc.)

After you set the recurrence, you have to write an office script that work with the table data. You can work with the dataBodyRange of the table by using the table's GetRangeBetweenHeaderAndTotal() method. And once you have that, you can resize the range to get the data you need. Next, you need to get the values which you can use with the GetValues method. GetValues returns a 2d array which you can't return from a PowerAutomate RunScript. Since you can't do that, but you can return a string, you get around that by converting the 2d array to a json string. You can see the code below:

    function main(workbook: ExcelScript.Workbook): string {
      let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet();
      //get table
      let tbl: ExcelScript.Table = sh.getTable("Table1");
      //get table's column count
      let tblColumnCount: number = tbl.getColumns().length;
      //set number of columns to keep
      let columnsToKeep: number = 3;
      //set the number of rows to remove
      let rowsToRemove: number = 0;
      //resize the table range
      let tblRange: ExcelScript.Range = tbl.getRangeBetweenHeaderAndTotal().getResizedRange(rowsToRemove,columnsToKeep - tblColumnCount);
      //get the table values
      let tblRangeValues: string[][] = tblRange.getValues() as string[][];
      //create a JSON string
      let result: string = JSON.stringify(tblRangeValues);
      //return JSON string
      return result;
    }

Once you created your script, consider naming it something you'll remember when you call it in PowerAutomate (I called mine getTableValues). Next, after the recurrence in PowerAutomate, add a Run Script step. Fill out the values and select the script like so:

Next, you have to create the script which takes the input returned from the previous script and completes the final steps. So the script has to have a parameter that takes the string returned from the previous script (I called it tableValues in mine). In the script, you have to parse the json string array to create a 2d array, resize the initial range, and then set the values of the resized range. You can see a script that does that below:

    function main(workbook: ExcelScript.Workbook, tableValues: string)
    {
      let sh: ExcelScript.Worksheet = workbook.getWorksheet("Sheet1")
      //parses the JSON string to create array
      let tableValuesArray: string[][] = JSON.parse(tableValues);
      //gets row count from the array
      let valuesRowCount: number = tableValuesArray.length - 1
      //gets column count from the array
      let valuesColumnCount: number = tableValuesArray[0].length - 1
      //resizes the range
      let rang: ExcelScript.Range = sh.getRange("A1").getResizedRange(valuesRowCount,valuesColumnCount)
      //sets the value of the resized range to the array
      rang.setValues(tableValuesArray)
    }

In PowerAutomate, you have to create a second run script step. In the second step, you should be prompted with a value to enter after you've selected the script (the value is called tableValues in my step.) In the table values input, you have to enter the dynamic content Result value. Once this is done, you can save the script and test.

One thing to note is that the second script doesn't delete old range values from previous runs. This can be done in a number of different ways. But the preferred way may depend on how the workbook is structured. So I'd recommend writing code to clear the range in the second script somewhere in the beginning. Or better yet, add the output of the first script into an Excel table. And just empty out the table every time you run the second script.

If you'd like to see how you might do that, you can take a look at this post here