Range setValues: The number of rows or columns in the input array doesn't match the size or dimensions of the range.

[gabi5rova]

Hi All,

I am trying out something new - office scripts to move excel data from one workbook to another. I found a great guide - https://exceloffthegrid.com/move-data-between-workbooks-power-automate/?unapproved=77662&moderation-...

And I have copied both scripts but for some reason I get an error:Office JS error: Line 22: Range setValues: The number of rows or columns in the input array doesn't match the size or dimensions of the range.

This is the script: and the error is in red at the bottom (line 22 is dataRng.setValues(dataArr))

I would be extremely grateful if anyone had any ideas.

function main(workbook: ExcelScript.Workbook, wsName: string,

  startCell: string, strArr: string) {

  //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 dataArr = ws.getRange(startCell).getSurroundingRegion().

    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)

}

[rzaneti · Accepted solution]

Hi @gabi5rova ,

I see that you are merging two ranges containing data ('dataArr' and 'newDataArr'). Your logic looks correct, and you are capturing the quantity of rows (dataArr.length) and columns (dataArr[0].length) when resizing your destination range. 

Do the ranges ('dataArr' and 'newDataArr') have the same quantity of columns?

[gabi5rova]

Hi @rzaneti ,

Thank you very much for the reply!

dataArr - has around 8 and the new data has 4. 

Do you think this could be the issue?

I forgot to mention that this is my previous step: It works fine and I have also included a screenshot of the flow.

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)

//Return the range
return { wsRngArr };

}

[gabi5rova]

I want to thank you so much! You have just opened my eyes 😄 Works like a charm when the column numbers are equal!

[geddb]

Hi @rzaneti ,

I was hoping you may help me understand this. I'm having the same problem with the same exercise. I can't for the life of me work out the problem or even understand how to begin troubleshooting the problem.

I understand that the combined array has a length or rows (dataArr.length) and a length of columns (dataArr[0].length) and that this line of code sets a range to paste the combined data into:

let dataRng = ws.getRange(startCell).getAbsoluteResizedRange(dataArr.length, dataArr[0].length);

I have tried this with the test data from the post and with my own data and I'm still getting the same error as @gabi5rova :

Range setValues: The number of rows or columns in the input array doesn't match the size or dimensions of the range

I have been working on this for days and I am getting nowhere. If you could help me understand why I'm getting this error (even help me understand how I can see what the sizes of the array and the range to paste into) I would be forever grateful.

Thank you so much in advance. I'm on a tight deadline and I just cannot get past this problem. I'm so frustrated and feeling utterly helpless.

[rzaneti]

Hi @geddb ,

Sure!

In Office Scripts, when you are adding a value to a multiple rows range with setRange() method, the expected input is an bidimensional array, or an "array of arrays", where each inner array will represent a row inserted in the Sheets, and each of its items the value added to the cell.

In this thread, the original problem was a difference in the array sizes (internal or external) when comparing with the target range: the size of the external array should be equal the quantity of rows in the range, while the size of the internal should be equal the quantity of columns. If you have any difference in these sizes, the method will fail.

To troubleshoot it, let's follow these steps:

- Add a new row in your code, before the setRange, containing console.log(dataArr.length). It will display the size of the external array in your console.

- Add a new row in your code, before the setRange, containing console.log(dataArr[0].length). It will display the size of the internal arrays in your console. Have in mind that all inner arrays must have the same size, as the range to be set is usually in a square format.

- Check if the range assigned to dataRng variable rows qty matches to external array size, and the columns qty to the internal array size. You can do it by adding this line of code: console.log(dataRng.getAddress()).

- If you have an error when accessing dataRng variable, use a console.log(dataArr) instead and analyze individually the internal array sizes (they will be displayed inside brackets, as in the image below).

Also, feel free to share these values (arrays size, dataRng range, and you code). 

Let me know if you need anything else!

http://digitalmill.net/ 
https://www.linkedin.com/in/raphael-haus-zaneti/ 

[geddb]

@rzaneti , thank you, thank you, thank you, for taking the time to explain this in such detail.

I couldn't get the console log output because the Office Script is receiving the data from a previous Office Script via Power Automate so when I run the script it errors at the conversion of the string array to and array:

let newDataArray: string[][] = JSON.parse(stringArray);

But that's OK, I finally understand the process and I will be forever overflowing with gratitude for your benevolence.

As it turns out, I was parsing the data array to an empty worksheet in a different workbook, but because it has predefined headers (that exceeded the number of columns in the data array) across the destination Excel worksheet, the procedure was failing. I thought because there was no data 'inside' the table, it should be fine, but now I understand it more completely, and it makes sense that I'm getting an array mismatch because getSurroundingRegion() is picking up the prepopulated column headers.

Once again my sincere gratitude for taking the time to give a wonderful and detailed explanation 🐵 

[biboy852]

Hello @rz, thank you so much for your help, I'm getting the same error based on the same article guidance. However, my question is it is possible to have different row name and different range number from the data worksheet that I want to compare? If so what would be the best office script row to add? Is the ranges going to be: 

dataRng.setValues(dataArr newDataArr)  

[geddb]

Hi again @rzaneti and other resident guru's that are waaayyy smarter than me :o/

I'm so sorry to do this to you but my array size mismatch is back again and I have no idea why. All I did was migrate the files that are being manipulated from the testing area to the live library and pointed the flows at the actual SharePoint Library that holds the live data instead of the test data. I haven't changed the construct of tables in the files in any way. The first live test worked, then the array mismatch error started.

Here is the first Script that gets the new data:

This is the second Script that joins the 2 arrays together and puts the old and new data back to the file...

Both the file with the new data and the file storing the old data have 8 columns, but both have arbitrary numbers of rows at each run.

The error message says the problem is on line 30 of the second script but I can't for the life of me work out why...

Any help will be warmly received.

Many thanks in advance.

[rzaneti]

Hi @biboy852 ,

Sorry for the late response. Could you please share your code? If you prefer, feel free to open a separated thread for this issue: you can mention me there. 

[rzaneti]

Hi @geddb ,

Can you please move your code lines 32 to 34 to above line 31? The idea here is to see the output of the "console.logs" before the setValues operation. Once you do it, please share the outputs here.

[ebcbmbxb]

Running into a power automate / script issue:

Pulling from from sheet on a workbook and then sending the data to another workbook/sheet.

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());

}

Import Script

function main(workbook: ExcelScript.Workbook, sheetName: string, rangeAddress: string, values: string) {

  let selectedSheet = workbook.getWorksheet(sheetName);

    let range = selectedSheet.getRange(rangeAddress);

    range.setValues(JSON.parse(values));

}

Import Script Setup in Automate:

Any help would be awesome!