cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

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)

 

}

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
rzaneti
Super User
Super User

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?

View solution in original post

11 REPLIES 11
rzaneti
Super User
Super User

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?

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_0-1695126163839.png

 

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

 

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.
 

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

rzaneti_1-1707738428644.png

 

 

- 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).

rzaneti_0-1707738237821.png

 

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/ 

@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 🐵 

 

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
Helper II
Helper II

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:

geddb_0-1714978108130.png


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

geddb_1-1714978197619.png


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...

geddb_2-1714978387929.png

Any help will be warmly received.

Many thanks in advance.




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. 

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
Regular Visitor

Running into a power automate / script issue:

 

ebcbmbxb_0-1715463543181.png

 

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

 

ebcbmbxb_1-1715463599591.png

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:
 
ebcbmbxb_2-1715463720608.png

Any help would be awesome!

 

 

 

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (1,637)