Error after Run the code

[Dave1023]

Type of issue

Code doesn't work

Feedback

After run the Workflow it appears this error BadRequest

Details of the Error { "message": "We were unable to run the script. Please try again.\nOffice JS error: Line 7: Workbook addWorksheet: The argument is invalid or missing or has an incorrect format.\r\nclientRequestId: eace1653-8ec2-4675-bb8e-636b07b9fd39", "logs": [] }

Page URL

https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/combine-worksheets-into-single-workbook#power-automate-flow-combine-worksheets-into-a-single-workbook

Content source URL

https://github.com/OfficeDev/office-scripts-docs/blob/main/docs/resources/samples/combine-worksheets-into-single-workbook.md

Author

@o365devx

Document Id

61518540-2796-b66f-4bc7-aef0eca420ad

[AlexJerabek]

Hi @Dave1023,

Thanks for reaching out. The cause of your error is most likely that the worksheet names are too long. Please refer to the troubleshooting guidance here: https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/combine-worksheets-into-single-workbook#troubleshooting

The final script to make shortened worksheet names (as suggested in the first bullet of the Troubleshooting section) looks like this:

/**
 * This script creates a new worksheet in the current workbook for each WorksheetData object provided.
 */
function main(workbook: ExcelScript.Workbook, workbookName: string, worksheetInformation: WorksheetData[]) {
  let worksheetNumber = 1;
  // Add each new worksheet.
  worksheetInformation.forEach((value) => {
    let worksheetName = `${workbookName}.${value.name}`;
    let sheet = workbook.addWorksheet(`${worksheetName.substr(0, 30)}${worksheetNumber++}`);

    // If there was any data in the worksheet, add it to a new range.
    if (value.data) {
      let range = sheet.getRangeByIndexes(0, 0, value.data.length, value.data[0].length);
      range.setValues(value.data);
    }
  });
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  name: string;
  data: string[][];
}

Please let me know if that helps. If you're still encountering problems beyond what the troubleshooting guide specifies, I'm happy to keep assisting you.

[Dave1023]

Thank you for the response, I did not used long sheet name, but I have changed to the default "Sheet1" and worked perfectly, I liked this flow a lot.

On Fri, May 3, 2024 at 9:48 AM Alex Jerabek @.***> wrote:

Hi @Dave1023 https://github.com/Dave1023,

Thanks for reaching out. The cause of your error is most likely that the worksheet names are too long. Please refer to the troubleshooting guidance here: https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/combine-worksheets-into-single-workbook#troubleshooting

The final script to make shortened worksheet names (as suggested in the first bullet of the Troubleshooting section) looks like this:

/**

• This script creates a new worksheet in the current workbook for each WorksheetData object provided. */ function main(workbook: ExcelScript.Workbook, workbookName: string, worksheetInformation: WorksheetData[]) { let worksheetNumber = 1; // Add each new worksheet. worksheetInformation.forEach((value) => { let worksheetName = ${workbookName}.${value.name}; let sheet = workbook.addWorksheet(${worksheetName.substr(0, 30)}${worksheetNumber++});

  // If there was any data in the worksheet, add it to a new range. if (value.data) { let range = sheet.getRangeByIndexes(0, 0, value.data.length, value.data[0].length); range.setValues(value.data); } }); }

// An interface to pass the worksheet name and cell values through a flow. interface WorksheetData { name: string; data: string[][]; }

Please let me know if that helps. If you're still encountering problems beyond what the troubleshooting guide specifies, I'm happy to keep assisting you.

— Reply to this email directly, view it on GitHub https://github.com/OfficeDev/office-scripts-docs/issues/738#issuecomment-2093276299, or unsubscribe https://github.com/notifications/unsubscribe-auth/BIHV6J2U5KM5RCN5TNPNPBTZAOWTTAVCNFSM6AAAAABHEMATAOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOJTGI3TMMRZHE . You are receiving this because you were mentioned.Message ID: @.***>

[AlexJerabek]

Thank you for confirming. It might also have been that the workbook name was long (since that gets used too). I'll close this issue for now and look into ways to make this flow more resilient without adding complexity.