66 questions
1
vote
0
answers
112
views
Excel & Office Script - Button "add in workbook" not visible - Impossible to share script or to attach it to a workbook
I wrote a simple Office Script to process data in a Excel workbook. (it's my first use of Office Script)
Code is simple and the script run without error and do what I was expecting.
But impossible to ...
- 31
0
votes
1
answer
128
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
131
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
1
vote
1
answer
216
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 ...
- 765
0
votes
0
answers
88
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
2
votes
2
answers
252
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
133
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" ...
1
vote
1
answer
74
views
Trouble parsing and changing Hyperlinks in a Word document
Let me preface this with the fact that I am aware that there's a lot of resources online for this. So many in fact, that I have trouble finding a solution for my particular issue.
The goal:
We have a ...
- 85
0
votes
1
answer
110
views
Vlookup using office script in PA Cloud returns NA values
I have created office script which vlookups 7/8 columns from another file in SharePoint. Whenever I run the script from Excel Desktop, the vlookups fetch data accurately.
But, if the same script is ...
- 43
0
votes
2
answers
148
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
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 ...
0
votes
0
answers
228
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
1
vote
2
answers
507
views
is there a function to fetch date values in excel using office script that will preserve the format?
main program objective : skim through a column of dates and identify the row of current date
below code snippet objective: to print the cell value in date format
function main(workbook: ExcelScript....
0
votes
1
answer
598
views
Set Array Values to Range as Text in Office Scripts
I am trying to copy data from one workbook to another using a combination of Office Scripts and Power Automate. Using office scripts, I was able to get the range text to an array. However, when ...
- 3
0
votes
1
answer
167
views
How to Get Range From Pivot To Copy to Another Sheet Using Office Scripts?
I want to get the range object of the Pivot based on a certain column. For example, I want to get the data for the pivot column 'Food'. On VBA, it is ptPivot.PivotFields("Food").DataRange
I ...
- 3
0
votes
1
answer
724
views
Office Script to format raw data and export to csv
I am new with Office Scripts. Here is what I am trying to accomplish
If A9 is 1, copy Raw_Data sheet F9 to J9 to temp sheet A1 to E1
If A10 is 2, copy Raw_Data E10 and I10 to temp sheet F1 and G1
If ...
- 25
1
vote
1
answer
141
views
getRange works when pasting from one sheet to next but not if getRangeByIndex
I am trying to understand why getRangeByIndex does not work when copy pasting from one sheet to another sheet
Here is the getRange script
function main(workbook: ExcelScript.Workbook) {
let temp = ...
- 25
1
vote
1
answer
1k
views
Is it possible to edit office scripts for Excel on the web in VS Code and use git for source control?
I am using a Microsoft 365 work account and I want to develop Office Scripts for Excel to automate monthly Excel tasks that I can also share with other co-workers. Sharing a Script has been fine. But ...
- 11
0
votes
1
answer
207
views
Office script to get data from table, filtered data and pasted into a new tab and new table
I have been trying to extract filtered data from my "PMs" table in my excel, I need to filter column D for statuses that are equal to "WSCH" or "WAPPR" or "INPRG&...
0
votes
1
answer
247
views
How to Read Excel Form Controls Using Power Automate or Office Scripts?
I have an Excel file that contains various Form Controls, such as buttons, checkboxes, and drop-down lists. I need to read the values and states of these Form Controls programmatically using either ...
0
votes
0
answers
48
views
How to add a row with formula to a protected worksheet using a Variable?
I have wrote a script to add a row with formula to a protected worksheet. The selectedSheet is mapped to the active sheet, which is password protected.
In the protected worksheet, there is a table for ...
- 1
0
votes
1
answer
360
views
Office Script get excel table rows from each worksheet and return to Power automate
I'm trying to get Excel table data from every worksheet in the file, to be returned in to Power Automate.
function main(workbook: ExcelScript.Workbook) {
let sheets = workbook.getWorksheets();
...
- 15
0
votes
1
answer
610
views
Loop through worksheets get data from table on each sheet
I'm trying to loop through worksheets in an excel file and get the data table rows from each sheet, to Power Automate.
I'm trying this in my script, but I need:
function main(workbook: ExcelScript....
- 15
0
votes
1
answer
213
views
Excel Office Script to remove rows with highlighted cells
I'm looking for help with an office script that will remove any rows from my table that contain highlighted cells. I tried using the recorder to filter rows with highlighted cells from one of my ...
- 3
0
votes
3
answers
54
views
How to convert a text with data in series having field name on left to a table in Excel?
I need to convert a dataset in series to a table in Excel. The dataset has field names on the left side in order and each dataset is one after another vertically. I want to have a common primary row ...
-1
votes
1
answer
171
views
Merge all excel sheets into a one new Sheet
I need your help, I have an Excel file type .xlsx, which was sent automatically from a system to my email, this Excel comes with the information distributed in several tabs within the same file, what ...
0
votes
0
answers
749
views
Excel Office Script - setFormula and SUMIFS not working
I am brand new to office script, I have two consecutive lines and am unclear on why I am getting an error on the SUMIFS line. The error is "Range setFormula: The argument is invalid or missing ...
- 1
0
votes
2
answers
368
views
Convert nested arrays to one-dimensional array in MS Office Script [duplicate]
Hi I need to convert this:
// Convert [[1],[2],[3],[4],["a","b","c"],["d","e","f"]]
to this:
[1,2,3,4,"a","b","c&...
- 15
0
votes
2
answers
392
views
Office Script count empty cells in a column
I have a script to count empty rows in a worksheet, but need a script to count empty cells in a column, the script I have is below and the column will always be column S, any ideas?
function main(...
0
votes
1
answer
1k
views
Excel Office Script - Does ".getVisibleView" return a valid range?
IIn excel, using an office script, I'm trying to copy a value from one cell in what I assume is a valid range to another. Simplified version of my code:
let selectedCell = workbook.getActiveCell();
...
- 140
0
votes
0
answers
225
views
Edit the "Row Labels" field of a PivotTable using ExcelScript
I am attempting to modify the Row Labels field of a Pivot Table using ExcelScript, but cannot modify the cell. When I attempt to modify it, I get the following error:
"The operation failed ...
user23192747
0
votes
1
answer
720
views
Share office scripts connected to Excel with external organisations
I have developed a tool in Excel that is based on Office scripts. The tool is developed for external organisations.
Unfortunately, office scripts attached to an Excel workbook can only be shared ...
- 115
-1
votes
1
answer
394
views
Optimizing Officescript for Power Automate
First post here.
I've been tasked with snapshotting some data in an Excel file and storing it for use in a Power BI report. I'm relatively new to Officescript and the Power Platform, with some ...
- 3
0
votes
1
answer
812
views
How to delete rows using Office Scripts based on dynamic column range in Excel?
I am working on a task in Excel using Office Scripts and I'm facing an issue with deleting rows based on a dynamic column range. Here's what I'm trying to achieve:
I want to delete entire rows below ...
- 19
0
votes
1
answer
397
views
Excel Office Script to Replace #N/A Values in a Column with Corresponding Values from Another Column
I'm working on an Excel Script using Office Scripts in Excel and facing an issue with replacing #N/A values in one column with corresponding values from another column. I have a table named "...
-3
votes
2
answers
2k
views
How to filter Positive/Negative/Zeros values in Excel column using Office Scripts?
I want to filter negative values, positive values and zeros separately using office scripts.
Below excel sheet "Difference" column has negative/positive/zeros.
What I want is,
Filter ...
- 19
-3
votes
1
answer
781
views
How to copy data from one column to another column using Office Scripts?
I'm currently working on automating some tasks in Microsoft Excel using Office Scripts, and I'm having trouble figuring out how to copy data from one column to another.
I want to copy Date column(D) ...
- 19
0
votes
1
answer
1k
views
Office Scripts Autofill value for a set range
I have about 10,000 rows of data and need to autofill the value 1,2,3...64 for 143 rows. So "1" will be autofilled for 143 rows, then "2" for 143 rows and so forth until the value &...
- 59
1
vote
1
answer
3k
views
How to get filtered range from Table's Auto Filter in Excel's Office Script?
Sorry, I am new to Office Scripts but have asked ChatGPT, Bing Chat and done lots of Google searches without much luck. I thought it should be something very simple for Microsoft to provide. I must ...
- 11
0
votes
1
answer
3k
views
Filtering and Adding Values to Specific Rows in Excel Using Office Scripts
I'm working with Microsoft Excel and trying to use Office Scripts to filter rows based on a specific year and months, and then add a value to a "Quarter 2" column for those filtered rows. ...
- 19
0
votes
1
answer
781
views
MS 365 Excel Scripts Error " Range clear: There was an internal error while processing the request."
I am working on a workbook that has several worksheets, I am using the following code to find the formulas on each sheet and convert then to their calculated values.
function main(workbook: ...
0
votes
0
answers
100
views
ExportAsFixedFormat method on Project
I have got the next error when I try to use Subroutine. The error is:
My code is:
Sub CreatePackages()
Dim folderPath As String
Dim matrix As Project
Dim template As Project
Dim templatePath As String
...
-1
votes
1
answer
617
views
Split multivalues in the same column in Office Script
I have following table in Excel. I would like to split the values in the column Product only based on delimiters comma, semicolon or pipe. The code is working but that it splits just multi values in ...
- 333
0
votes
1
answer
51
views
0
votes
0
answers
43
views
Copy data from multiple sheets into single sheet [duplicate]
I'm trying to copy the excel multiple worksheets data into single worksheet using office scripts.can anyone tell how to do it?
I tried getting first range of the sheets after loop through the sheets ...
0
votes
1
answer
339
views
Table column data used as PivotTable filter?
In a worksheet titled 'Config' I have several lists of names in a table with several columns denoting different teams, for the sake of example lets say:
Team A
Team B
Alan
Dane
Geof
Keri
This team ...
- 1
0
votes
1
answer
6k
views
How do I select an entire column from a range?
I am trying to identify all the cells in a column that have the following and change the font color to red.
a space
a hyphen
an underscore
more than 16 characters in a cell
This is my code
function ...
0
votes
1
answer
609
views
Adding QR code on my Excel Online using office script and Powerautomate
I am using the following code to insert the QR code on my workbook, while this code work when i run it from office script, I am not able to run this script from Power automate because it looks like ...
- 19
1
vote
1
answer
1k
views
Adding Image from URL to specific Cell using Office script
I am trying to add a QR Code on specific Cell using office script, The following code works fine but, I am not able to figure out how to specify the position(cell G1), I want that QR Code top left ...
- 19
1
vote
1
answer
379
views
Create 1 ReturnObject from 2 JSON object arrays with Typescript
I'm trying to create a JSON with an Excel office script, to use this JSON in a Microsoft Power Automate flow to create Item master data . My problem is that I don't get the message header and the Item ...
- 11