487 questions
0
votes
0
answers
53
views
Office Scripts manual filter on pivot tables failing in browser excel version
I've used the below Office Script code successfully in the past to apply a manual filter against a filter hierarchy in a pivot table using office scripts. The below works in the app version, but it is ...
- 3
0
votes
1
answer
84
views
How can I hide or collapse subtotal rows in an Excel PivotTable?
I'm wondering if this is possible, as with pivot tables in excel there is an option to expand or collapse all fields. If you try to record this in Automate, it says this action cannot be recorded. A ...
- 3
0
votes
1
answer
91
views
Excel Office Script (not VBA!) to hide / show columns, depending on cell value or filtered table
I need to build an Office Script (not VBA) to help a number of users hide / show columns on a Cloud-shared worksheet, in a controlled and structured way (so, not by manually using the Excel menu Sort ...
- 15
0
votes
1
answer
259
views
Excel Office Script works on Desktop but fails on Excel Online with "An unexpected error occurred"
I am tyring a POC where my power bi gets refreshed via ADF and then i want to trigger a power automate flow which runs a script inside my analyze excel file. When i run the script manually in desktop ...
- 13
1
vote
0
answers
77
views
Custom Format footer with Office Script
I'm using Office Scripts for Excel on the web and trying to insert a logo image into the footer and apply custom text formatting (font, size, and color).
So far, I've successfully set the font and ...
- 123
1
vote
1
answer
69
views
Why do I get an error by trying to add a formula in a cell in Excel Script?
I'm trying to add a formula to a specific cell in my French Excel, but I get an error when I try to run it. Any ideas on what to change?
Just to add, I got this English formula by using the "...
0
votes
0
answers
230
views
Convert Date to Specific Format - Office Scripts
I am trying to compare dates in a worksheet range and am struggling to convert Today's Date into the format DD-MMM-YY (i.e. "9-Jul-25"). Can anyone help with how I need to convert this? ...
- 3
0
votes
0
answers
58
views
Data Aggregation using office scripts for Power Automate
I have an array in below format (from column A to D) in Power Automate:
Not all entities will be part of a group but each group will have a group representative. A group can have just one entity and ...
0
votes
0
answers
101
views
Get minimum / maximum value of range
I need to the the minimum (or maximum, or some other values) of a range of data using Office Script in Excel. My data (a column of a table) contains only numeric values or empty cells.
I can loop over ...
- 31.3k
0
votes
1
answer
82
views
Can I check if cells are a Date and change their format?
I recorded the macro and then did some changes so they would show the right date format. The problem is that depending on the report the dates are located in different columns, I would like to create ...
- 15
0
votes
1
answer
120
views
Office Script: different array length results between getValues() and looping
I'm stuck with the logic in Office Scripts. Following function is running on a worksheet
in a dynamic range, currently "A2:BF21"
that has the data is organized in columns, with different ...
- 93
0
votes
2
answers
125
views
Office script does not return array
I'm working on an Office Script and got stuck where one function returns an array correctly, whereas the other function does not return the array.
Below is my complete code.
cleanSheet function ...
- 93
0
votes
0
answers
66
views
ExcelScript Functions Properly when run in Excel, only partially runs when called by Power Automate?
I have an extremely simple ExcelScript that I use to copy cells from one sheet to another. It works with zero issues when called from within Excel itself - both from the desktop application and the ...
1
vote
1
answer
206
views
How to set a hardcoded formula to dynamic in Excel office script?
I're recorded an action in office script and this is one part that was recorded.
selectedSheet.getRange("AN4:AN5").setFormulas([["=AN2-AM2"],["=AN3-AM3"]]);
I wanted to ...
- 757
1
vote
2
answers
202
views
How to get last column in specific range in Office Script?
I have values in range A1:E10 and I'd like to get the next empty column in that range (excluding headers). In this case last column used is "C" and next empty column would be "D".
...
- 757
0
votes
0
answers
82
views
Custom events for Office Scripts?
Per organization policies, I can't develop Excel add-ins. I can use Office Scripts, but there are no event handlers for Excel objects via this API. Is there any use and/or is it even possible to ...
- 1
1
vote
1
answer
97
views
Office Scripts: trying to add slicer to pivot table
I am trying to insert a slicer to filter a pivot table using Office Scripts. However, I always receive a run time error when using the below code. The objects passed to the add method seem to exist, ...
- 25
2
votes
1
answer
232
views
ExcelScript fails on webapp, succeeds on desktop
Last year I wrote a simple script to keep a locked sheet protected but allow users to insert new rows into a table when they press the designated button. It always succeeded with few errors, normally ...
0
votes
0
answers
86
views
Trying to make a basic auth API call from Office Script
Using this and it fails to fetch. Where am I missing it?
async function main(workbook: ExcelScript.Workbook) {
const response = await fetch('https://postman-echo.com/basic-auth', {
method: 'GET',
...
- 1
1
vote
1
answer
72
views
How to paste 1-D array into range in office script?
I'm very new to office scripts. I have 1-Dimensional array that I want to paste in a range, but I'm getting the error
Line 7: Range setValues: The argument is invalid or missing or has an incorrect ...
- 757
0
votes
2
answers
201
views
Office scripts, use Variable to determine a Row inside a SetFormulaLocal If
I'm trying to determine a Row inside of an If clause, i already know the column, it will always be E, but the row will change depending on what the active cell is,
this is what i have:
let ...
2
votes
2
answers
238
views
Creating an office script where it looks at the first column in each sheet and inserts a column based on the value in the first column
I need help creating an Excel office script that can automate some formatting within each sheet of the workbook.
I need to place a blank column in all of the worksheets within the workbook.
I am ...
- 23
0
votes
1
answer
128
views
Microsoft Office Scripts - Move cell value based on another cell's value
I'm looking to loop through each row and based on a cell value I want to move another cell's value to new cell.
Example: Loop through each row if column "Method" has a value "D" ...
0
votes
1
answer
168
views
Change color of specific column in Excel chart with Office Scripts?
I'm trying to color a specific column on a column chart in Excel for web using Office Scripts. I'm able to set the color of all columns using this code:
function main(workbook: ExcelScript.Workbook) {
...
- 1
0
votes
1
answer
75
views
Set DataValidation in a list of columns at once
I'm trying to rewrite some old VBA code to Office Script.
One of the steps is to set data validation on several columns.
With the script I am able to loop all columns of a named table.
So my idea was ...
- 3
0
votes
0
answers
59
views
How do I multiple an Excel Range by 1 using Office Script?
How can I multiple the selected range in Excel using Office Script by 1?
I'm trying to use the Excel trick of converting dates and numbers that aren't dates and numbers into being so by multiplying by ...
- 11
0
votes
2
answers
242
views
Problem with populating a created excel table with data from a transposed HTML table using power automate
I have created a transposed HTML table in power automate that looks like the following table:
My problem is trying to create an excel table in power automate to populate the above data given that the ...
- 59
0
votes
0
answers
117
views
Update Link using OfficeScript
I have a VBA macro created for excel where formula have linkage to external links so I use
Myworkbook.UpdateLink Name:=Myworkbook.LinkSources
Now we are planning to move the macro to OfficeScript so ...
- 43
1
vote
1
answer
66
views
OfficeScript says it is writing to designated excel sheet, but no text appears and no errors given
I cannot get my office script to write any data in the "Data" sheet. I get no errors from running the script and it does say it writes the data but then nothing is there... I've tried ...
0
votes
1
answer
229
views
Error on numberFormatLocal on Office Scripts
I want to automize some format work in one of my excel files. Therefore I used macros for a long time - now it's time to move to scripts I thought.
In the old world the cutsom style formating was done ...
- 3
0
votes
0
answers
132
views
Excel script conditionalformating 'set formula' not working as expected
I wrote a script with conditionalformatting rules, everything works fine. But I have some lines that don't work when I run the script... unless I go to Conditional Formating ->manage rules, where I ...
0
votes
2
answers
143
views
Office Scripts Rows not Copy Pasting Correctly
Goal is to find any rows in a table "PO" on sheet that's value is "Cancelled," "Ordered," or "Voided" in column A, copy the row, then paste it onto the end of ...
0
votes
1
answer
81
views
How to apply conditional formatting only on the top level rows in the pivot table hierarchies
I’m trying to find the addresses of all the top-level rows in hierarchies to apply conditional formatting to the pivot table. It seems that using the method below, I can only extract the definitions ...
- 636
0
votes
1
answer
1k
views
Office Scripts cannot read properties of undefined
I am writing a program that sorts my table then splits the strings in column E of the table, cutting the values in column E down to the first 10 chars and putting the remainder in column F.
function ...
1
vote
1
answer
121
views
Creating a formula in the script that changes rows like auto-fill does
I have some coding experience but not much with this language. I am trying to create a script that will automatically copy a formula down column H, the only problem is that it is referencing that same ...
- 11
0
votes
1
answer
382
views
Offset getUsedRange in Office Script with a starting address
Need a sub-range of the used range (sheet.getUsedRange()) starting from a specific address (ex. A10)?
- 405
0
votes
2
answers
105
views
I need to change also a cell with the same value as the sheet name excel
I wrote this code to create sheets taking the name from a range, i need to also change a specific cell in the new sheet with the same name of the created sheet
function main(wb: ExcelScript....
- 11
0
votes
1
answer
143
views
Copy a row from one sheet to another dependent on a dropdown selection and hide on the original sheet
I have an Excel file - with 2 sheets - on the first sheet - with many rows with loads of data - the final row will have a dropdown selection of "None" / "Active" / "Closed&...
0
votes
0
answers
76
views
Can an office 365 addin write and execute an office script?
I have an Excel spreadsheet for which I have written an office 365 addin hosted in Microsoft Azure. The addin creates a Taskpane for the user which makes their interactions with the spreadsheet more ...
- 11
0
votes
1
answer
867
views
Excel ONLINE Copy Selection Range to Clipboard
Using the "online" version of Microsoft Excel and its automation tool "Code Editor" I want to create a script which automatically selects a range and copies the values to the ...
1
vote
1
answer
368
views
How to apply custom text format with Excel Scripts
I am trying to set up a custom format using Office scripts, so that when the number 10 is entered in a cell, it displays the string ten instead. Of course, the method will work for any number/string ...
- 25
0
votes
1
answer
106
views
Office Script to delete rows older than today
I have a vehicle booking sheet in excel has column A as the date range. Currently I use macros to delete any rows that are older than Today() when the excel doc is opened to remove old data. We soon ...
0
votes
1
answer
122
views
Delete a specific value from one column and Rename another Value in Excel using office script
I need help on an ExcelScript where I need the Column J filter then
Delete the entire Row in column J with the value "Canceled"
Renamed any value in column J with "In Progress" to &...
1
vote
2
answers
99
views
Why does this comment change the functionality of this code?
Edit: Realized the "Exclude" column in my original question wasn't contributing to the issue at all. Simplified things even more and added full code that reproduces the error.
In Excel I ...
- 173
0
votes
0
answers
227
views
Get charts from sheet and send to email
I have the following code below.
const SHEET_NAMES = ["BaseGraficos"];
function main(wb: ExcelScript.Workbook): string {
const sheets = SHEET_NAMES.map((name) => wb.getWorksheet(name))...
- 1
0
votes
1
answer
790
views
I keep getting error - Range setFormula: The argument is invalid or missing or has an incorrect format
function main(workbook: ExcelScript.Workbook) {
let sheet1 = workbook.getWorksheet("Sheet1");
let usedRange = sheet1.getUsedRange();
let rowCount: number = usedRange.getRowCount()...
0
votes
1
answer
252
views
Chart Formatting in Excel Scripts
im relativley new to Excel Scripts and i need your help.
I have build an Excel Script which i want to add to my power automate flow, to generate graphs automatically with a predefined template.
In ...
- 3
1
vote
3
answers
728
views
Duplicate and rename sheets by script
I work with excel 365 online.
I am trying to duplicate a certain number of times a template sheet that I then have to rename by the value of a cell.
I have tried and tested several codes but currently,...
- 145
0
votes
1
answer
135
views
Automate sorting list of dates by day instead of chronologically, microsoft excel
I work at a company who sends out birthday cards to our clients on their birthdays. Every month we grab all of the birthdays which happen during the oncoming month and place them into different excel ...
- 1
0
votes
1
answer
676
views
delete excel column using office scripts
I am trying to do a simple delete of column A through column E but can't seem to get anything working.
I tried the record feature to insert code but this comment is all that gets inserted. I have ...
