Hi I have set up multiple office scripts to automate mapping data.
We have a \"Master sheet\" which is the source to all of the data required. I have then used formulas to pull over the relevant information based on the column title and align it etc. There are a lot of demands and it has been working fine but i now need this to run up to row 5000 and this has obviously slowed down the script.
As a result I now need to revise the full script to maximise efficiency where I can. Im very new to script hence why i'm using formulas to do a lot of the fetching and the work where i'm sure there is another solution.
any help would be appreciated. Script is below:
Great to know you sorted that out. Yes, performance shall be better. In addition you may adjust conditional formatting, but that's more cosmetic. Something like
\n\nfunction main(workbook: ExcelScript.Workbook) {\n let selectedSheet = workbook\n .getActiveWorksheet();\n\n const source = workbook\n .getWorksheet(\"Sheet1\")\n .getRange(\"A1:BC1\")\n .getUsedRange()\n .getValues()[0]\n \n const maxRowsInSource = 10000\n\n const targetNames = selectedSheet\n .getRange(\"A1:BC1\")\n\n const numberOfNames = targetNames\n .getCellCount()\n\n const headers = targetNames\n .getValues()[0]\n\n for (let i = 0; i < numberOfNames; ++i) {\n targetNames\n .getCell(0, i)\n .copyFrom(\n workbook\n .getWorksheet(\"Sheet1\")\n .getRangeByIndexes(\n 0,\n source.indexOf( headers[i] ),\n maxRowsInSource,\n 1\n )\n .getUsedRange()\n )\n }\n\n const duplicateRule: ExcelScript.ConditionalPresetCriteriaRule = {\n criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues\n }\n\n const blankRule: ExcelScript.ConditionalPresetCriteriaRule = {\n criterion: ExcelScript.ConditionalFormatPresetCriterion.blanks\n }\n\n const rangesBlank = selectedSheet\n .getRanges(\"F2:F3000, I2:I3000\")\n\n const rangesDuplicate = selectedSheet\n .getRanges(\"F2:F3000\")\n\n rangesBlank.clearAllConditionalFormats()\n rangesDuplicate.clearAllConditionalFormats()\n\n const conditionalFormatBlanks = rangesBlank.addConditionalFormat(\n ExcelScript.ConditionalFormatType.presetCriteria);\n const conditionalFormatDuplicate = rangesDuplicate.addConditionalFormat(\n ExcelScript.ConditionalFormatType.presetCriteria);\n\n const presetFormatBlank:\n ExcelScript.PresetCriteriaConditionalFormat = \n conditionalFormatBlanks.getPreset();\n presetFormatBlank.getFormat().getFill().setColor(\"lightyellow\");\n\n const presetFormatDuplicate:\n ExcelScript.PresetCriteriaConditionalFormat =\n conditionalFormatDuplicate.getPreset();\n presetFormatDuplicate.getFormat().getFill().setColor(\"red\");\n presetFormatDuplicate.getFormat().getFont().setColor(\"white\");\n\n presetFormatBlank.setRule(blankRule);\n presetFormatDuplicate.setRule(duplicateRule);\n\n}
","body@stringLength":"2609","rawBody":"Great to know you sorted that out. Yes, performance shall be better. In addition you may adjust conditional formatting, but that's more cosmetic. Something like
\nLet me try for the one column first. Assuming name in G1 of active sheet we find the column with the same name in Sheet1 and copy it to the column G of active sheet
\n\nfunction main(workbook: ExcelScript.Workbook) {\n let selectedSheet = workbook\n .getActiveWorksheet();\n\n const source = workbook\n .getWorksheet(\"Sheet1\")\n .getRange(\"A1:BC1\")\n .getUsedRange()\n .getValues()[0]\n\n const target = selectedSheet\n .getRange(\"G1\")\n\n selectedSheet\n .getRange(\"G1\")\n .copyFrom(\n workbook\n .getWorksheet(\"Sheet1\")\n .getRangeByIndexes(\n 0,\n source.indexOf(target.getValue()),\n 10000,\n 1)\n .getUsedRange()\n )\n}
\nif so based on this we may iterate all names in active sheet.
","body@stringLength":"1055","rawBody":"Let me try for the one column first. Assuming name in G1 of active sheet we find the column with the same name in Sheet1 and copy it to the column G of active sheet
\nif so based on this we may iterate all names in active sheet.
","author":{"__ref":"User:user:521"},"isEscalated":null,"postTime":"2024-02-05T06:55:02.774-08:00"},"ForumReplyMessage:message:4049240":{"__typename":"ForumReplyMessage","id":"message:4049240","revisionNum":1,"uid":4049240,"depth":4,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:4048981"},"subject":"Re: Increase Office Script Run Speed","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4049240"},"body":"SergeiBaklan Sorry im getting this error
","body@stringLength":"747","rawBody":"
","author":{"__ref":"User:user:2079146"},"isEscalated":null,"postTime":"2024-02-05T07:07:41.125-08:00","parent":{"__ref":"ForumReplyMessage:message:4049218"}},"ForumReplyMessage:message:4049298":{"__typename":"ForumReplyMessage","id":"message:4049298","revisionNum":1,"uid":4049298,"depth":5,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:4048981"},"subject":"Re: Increase Office Script Run Speed","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4049298"},"body":"\n
Just in case attached is the file on which I tested
","body@stringLength":"280","rawBody":"Just in case attached is the file on which I tested
","author":{"__ref":"User:user:521"},"isEscalated":null,"postTime":"2024-02-05T07:59:28.274-08:00","parent":{"__ref":"ForumReplyMessage:message:4049240"}},"ForumReplyMessage:message:4049901":{"__typename":"ForumReplyMessage","id":"message:4049901","revisionNum":1,"uid":4049901,"depth":6,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:4048981"},"subject":"Re: Increase Office Script Run Speed","readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:4049901"},"body":"Thank you, ive got that to work. Do you think this will work better/more efficient than using the formulas?","body@stringLength":"107","rawBody":"Thank you, ive got that to work. Do you think this will work better/more efficient than using the formulas?","author":{"__ref":"User:user:2079146"},"isEscalated":null,"postTime":"2024-02-06T00:18:30.327-08:00","parent":{"__ref":"ForumReplyMessage:message:4049298"},"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"Revision:revision:4050033_1":{"__typename":"Revision","id":"revision:4050033_1","lastEditTime":"2024-02-06T02:34:49.800-08:00"},"QueryVariables:ReplyList:message:4050033:1":{"__typename":"QueryVariables","id":"ReplyList:message:4050033:1","value":{"id":"message:4050033","sorts":{"postTime":{"direction":"DESC"}},"useAvatar":true,"useAuthorLogin":true,"useAuthorRank":true,"useBody":true,"useKudosCount":true,"useTimeToRead":false,"useMedia":false,"useReadOnlyIcon":false,"useRepliesCount":false,"useSearchSnippet":false,"useAcceptedSolutionButton":true,"useSolvedBadge":false,"useAttachments":false,"attachmentsFirst":5,"useTags":false,"useNodeAncestors":false,"useUserHoverCard":true,"useNodeHoverCard":false,"useModerationStatus":true,"usePreviewSubjectModal":false,"useMessageStatus":true}},"ROOT_MUTATION":{"__typename":"Mutation"},"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1737115705000":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/users/UserAvatar-1737115705000","value":{"altText":"{login}'s avatar","altTextGeneric":"User's avatar"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1737115705000":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/ranks/UserRankLabel-1737115705000","value":{"altTitle":"Icon for {rankName} rank"},"localOverride":false},"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1737115705000":{"__typename":"CachedAsset","id":"text:en_US-components/messages/AcceptedSolutionButton-1737115705000","value":{"accept":"Mark as Solution","accepted":"Marked as Solution","errorHeader":"Error!","errorAdd":"There was an error marking as solution.","errorRemove":"There was an error unmarking as solution.","solved":"Solved"},"localOverride":false},"CachedAsset:text:en_US-components/messages/LinearReplyList-1737115705000":{"__typename":"CachedAsset","id":"text:en_US-components/messages/LinearReplyList-1737115705000","value":{"title":"{count, plural, one{# Reply} other{# Replies}}","title@board:BLOG":"{count, plural, one{# Comment} other{# Comments}}","title@board:TKB":"{count, plural, one{# Comment} other{# Comments}}","title@board:IDEA":"{count, plural, one{# Comment} other{# Comments}}","title@board:OCCASION":"{count, plural, one{# Comment} other{# Comments}}","noRepliesTitle":"No Replies","noRepliesTitle@board:BLOG":"No Comments","noRepliesTitle@board:TKB":"No Comments","noRepliesTitle@board:IDEA":"No Comments","noRepliesTitle@board:OCCASION":"No Comments","noRepliesDescription":"Be the first to reply","noRepliesDescription@board:BLOG":"Be the first to comment","noRepliesDescription@board:TKB":"Be the first to comment","noRepliesDescription@board:OCCASION":"Be the first to comment","noRepliesDescription@board:IDEA":"Be the first to comment","messageReadOnlyAlert:BLOG":"Comments have been turned off for this post","messageReadOnlyAlert:TKB":"Comments have been turned off for this article","messageReadOnlyAlert:IDEA":"Comments have been turned off for this idea","messageReadOnlyAlert:FORUM":"Replies have been turned off for this discussion","messageReadOnlyAlert:OCCASION":"Comments have been turned off for this event"},"localOverride":false},"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1737115705000":{"__typename":"CachedAsset","id":"text:en_US-components/tags/TagView/TagViewChip-1737115705000","value":{"tagLabelName":"Tag name {tagName}"},"localOverride":false}}}},"page":"/forums/ForumMessagePage/ForumMessagePage","query":{"boardId":"excelgeneral","messageSubject":"increase-office-script-run-speed","messageId":"4048981","replyId":"4050033"},"buildId":"_FASV5DDw52YaqfBDLqsB","runtimeConfig":{"buildInformationVisible":false,"logLevelApp":"info","logLevelMetrics":"info","openTelemetryClientEnabled":false,"openTelemetryConfigName":"o365","openTelemetryServiceVersion":"24.11.0","openTelemetryUniverse":"prod","openTelemetryCollector":"http://localhost:4318","openTelemetryRouteChangeAllowedTime":"5000","apolloDevToolsEnabled":false},"isFallback":false,"isExperimentalCompile":false,"dynamicIds":["./components/seo/QAPageSchema/QAPageSchema.tsx","./components/community/Navbar/NavbarWidget.tsx","./components/community/Breadcrumb/BreadcrumbWidget.tsx","./components/customComponent/CustomComponent/CustomComponent.tsx","./components/messages/TopicWithThreadedReplyListWidget/TopicWithThreadedReplyListWidget.tsx","./components/external/components/ExternalComponent.tsx","./components/messages/MessageView/MessageViewStandard/MessageViewStandard.tsx","../shared/client/components/common/List/UnwrappedList/UnwrappedList.tsx","./components/tags/TagView/TagView.tsx","./components/tags/TagView/TagViewChip/TagViewChip.tsx"],"appGip":true,"scriptLoader":[{"id":"analytics","src":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/pagescripts/1729284608000/analytics.js?page.id=ForumReplyPage&entity.id=board%3Aexcelgeneral&entity.id=message%3A4050033","strategy":"afterInteractive"}]}