User Profile
djmitz6
Copper Contributor
Joined 6 years ago
User Widgets
Recent Discussions
- 2.4KViews0likes3Comments
Re: Excel Script ReplaceAll ignore formulas
Yes, my script is currently working as desired just looking for a more efficient solution. The problem I was having is if I didn't copy and paste the formula results, the formulas themselves would be come the cell value with additional apostrophes. It would have really been nice if the replaceAll function had an option to ignore formulas.2.3KViews0likes5CommentsRe: Excel Script ReplaceAll ignore formulas
The initial question never had the requirement to maintain the formula in the resultant table. (Maybe I should have been clearer on this.) I just needed the formula result. Since I do not push the formula to the SQL table, I only push it's value.2.4KViews0likes7CommentsRe: Excel Script ReplaceAll ignore formulas
SergeiBaklan I guess I could have been a little clearer and included my entire code. My Goal: To create a Universal function that allows me to take excel workbooks from emails that do not have excel formatted tables (just ranges) and convert them into tables in excel. That way I can use Power Automate to push the tables to my SQL Table. Here was the temporary solution I came up with to solve my first post problem; however, it doesn't feel very efficient to me. I am also super concious about data efficiency as I often hit the Power Automate run script limits with the Excel API. function main(workbook: ExcelScript.Workbook, WorkSheet_Name: string, DeleteTopRows: number) { let selectedSheet: ExcelScript.Worksheet; if (WorkSheet_Name == "") { selectedSheet = workbook.getWorksheets()[4]; } else { selectedSheet = workbook.getWorksheet(WorkSheet_Name); } if (DeleteTopRows > 0) { //Delete Top N Rows selectedSheet.getRange("1:" + DeleteTopRows).delete(ExcelScript.DeleteShiftDirection.up); } else { } // Clear Any Filters on Selected Sheet selectedSheet.getAutoFilter().remove(); // Get used Range let usedRange = selectedSheet.getUsedRange(); usedRange.copyFrom(usedRange,ExcelScript.RangeCopyType.values,false,false) // Add a new table let newTable = workbook.addTable(usedRange, true); // Replace SQL Reserved Characters selectedSheet.replaceAll("'", "''", { completeMatch: false, matchCase: false }) // Create ID column for Power Automate ODATA Filter newTable.addColumn(-1, Number[0], "ID"); let ID = newTable.getColumnByName("ID").getRangeBetweenHeaderAndTotal(); ID.setNumberFormatLocal("0"); ID.setFormula("=ROUNDUP((ROW()-1)/5000,0)") // Return Table ID to Power Automate return newTable.getId(); }2.3KViews0likes9CommentsExcel Script ReplaceAll ignore formulas
Greetings, I'm currently running an excel script where I am replacing all single quote characters with two single quote characters consecutively so that I can push the data in a table over to an SQL server utilizing Power Automate. One issue that I am experiencing is that when utilizing the below script it also replaces the characters inside of formulas: // Replace SQL Reserved Characters selectedSheet.replaceAll("'", "''", { completeMatch: false, matchCase: false }) Example of how the formula in one of the cells originally existed: =XLOOKUP(E4,'Sheet2'!E:E,'Sheet2'!F:F) Example of how the formulas get formatted after running the script: =XLOOKUP(E4,''Sheet2''!E:E,''Sheet2''!F:F) Is there anyway to get the replaceAll function to ignore characters inside of formulas? I'm trying to eliminate the need to individually iterate through each cell as this is resource intensive. I also thought about just copying the range and pasting back the values only, but I feel this will also be resource intensive.3.8KViews0likes19CommentsForms Responses Excel File Corrupt
Greetings, I believe that Forms has an error when I try to download my responses in an excel file from forms. The file size out of forms is 67MB, but when I trick Forms and force it to only download 5000 rows at a time by inserting ?DownloadFileSize=5000 directly into my HTML after .aspx it saves two files that are around 1.7MB each or 3.64MB when I manually combine them in excel. Any idea what could be causing this issue. It looks to be a Forms Excel generation error. Currently I have the excel spreadsheet linked to sharepoint as it is part of a sharepoint team. If I manually replace this excel spreadsheet in sharepoint will it throw off my forms data? Thank you!2KViews0likes1CommentBulk Edit Onedrive for Business
Greetings, I have a lot of folders that contain numerous .pdf files. I added a column into OneDrive for business to show a document title. Now the only way I have been able to edit the information in the document title page is by returning to classic onedrive view -> selecting a single file -> right-clicking -> select properties -> then modify the data. When I select multiple files the properties option goes away. Is there anyway to bulk edit .pdf files document properties in onedrive for business? In particular I have a list of document titles that I want to add to OneDrive for Business that currently reside in a excel spreadsheet.1.3KViews0likes0Comments
Recent Blog Articles
No content to show