Git Product home page Git Product logo

office-scripts-docs-reference's Introduction

Office Scripts API Reference

Welcome to the Office Scripts API reference documentation repository. For the best experience, we recommend you view this content on Microsoft Learn.

NOTE: You can find source files for Office Scripts documentation, including tutorials and how-to guides, in the OfficeDev/office-scripts-docs GitHub repository.

Give us your feedback

Your feedback is important to us.

  • To let us know about any questions or issues you find in the docs, submit an issue in this repository. Make sure you state the version + build number of the client you are using, and provide repro steps, console output, and error messages, as appropriate.

  • We also welcome your contributions to this documentation. To contribute, fork this repository, update the files as you deem necessary, and submit a pull request with your proposed changes. For details, see Contribute to this documentation.

    IMPORTANT: Do not modify files within the /docs/docs-ref-autogen folder of this repository. All of the files in that folder are autogenerated, so it is not possible to update them via pull request. To request a change to any of the files in the /docs/docs-ref-autogen folder, please submit an issue in this repository.

  • To let us know about your programming experience, what you would like to see in future versions, code samples, and so on, please submit an issue in this repository.

Microsoft Open Source Code of Conduct

This project has adopted the Microsoft Open Source Code of Conduct. For more information, see the Code of Conduct FAQ, or contact [email protected] with any additional questions or comments.

office-scripts-docs-reference's People

Contributors

alexbuckgit avatar alexjerabek avatar angryberryms avatar dependabot[bot] avatar elizabethsamuel-msft avatar holdyourwaffle avatar kenanlv avatar lindalu-msft avatar microsoft-github-policy-service[bot] avatar rick-kirkham avatar v-pegao avatar zhngx avatar

Stargazers

avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar

Watchers

avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar

Forkers

zhngx isabella232 dcthegeek kenanlv dancaires alainj-m ops-e2e-ppe tryweirdier mleach26 sbruhov alexbuckgit bobbytreed classicvalues eric54205420 peiche-jessica superyyrrzz hanqingzheng-ms aiminnovations holdyourwaffle meganbradley

office-scripts-docs-reference's Issues

Placement values are incorrect

[Enter feedback here]

When I am running the setPlacement method, there's inconsistency from the documentation and the result.

image

oneCell is documented as moved and sized with cells but the actual effect is move but don't size with cells.
twoCell is documented as moved with cells but the actual effect is moved and sized with cells.

oneCell and twoCell are interchanged

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

ExcelScript.PivotHierarchy in the API documentation but isn't recognized?

Received this feedback from an Office Scripts user: https://www.reddit.com/r/excel/comments/n25gvp/excelscriptpivothierarchy_in_the_api/?utm_source=share&utm_medium=web2x&context=3

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

Lack of TypeScript null safety

Article URL

https://github.com/OfficeDev/office-scripts-docs-reference/blob/main/generate-docs/script-inputs/excel.d.ts

Issue

Office Scripts TypeScript types provide no null safety — mostly not clear where nulls are/aren't allowed or occur:

  • Many return values might be null, but not typed as such. Often this is documented in JSDoc, but not reflected in the types. For example, DataValidation#getValid is typed () => boolean, whereas it's really () => boolean | null.

  • Many parameters are nullable, but not typed as such. For example, the extremely useful Table#setPredefinedTableStyle(null) to remove all table styles is completely undocumented (parameter is typed as string).

  • The inline Code Editor in Excel also provides no null safety. For example:

    const nullable = Math.random() > 0.5 ? 1 : null
nullable.toString()

    Here, nullable is typed as 1, and nullable.toString() will throw a runtime error 50% of the time.

    Presumably this is a tsconfig issue, so maybe outside the scope of documentation, but seems the issues are related.

Office script docs lack examples

I'd like to see simple -relevant- example code for every property and method on this page!
J.K. Pieterse
Office Apps & Services MVP

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

Update getCommentbyCell description to document error behaviour

The GetCommentByCell will throw a breaking error "getCommentByCell: The requested resource doesn't exist." if the comment does not exist at the target cell, instead of returning null (as expected from getter methods).
Please document this behavior or log a bug with the developers if this is a bug

https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.worksheet?view=office-scripts#excelscript-excelscript-worksheet-getcommentbycell-member(1)

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

Using `excel.d.ts` in another project

office-js-docs-reference uses DT as the source of truth for its type definitions, which can be conveniently installed in a project with @types/office-js. Is there an equivalent for Office Scripts?

There seem to be two versions of excel.d.ts in this repository:

The latter seems to get generated based on the first by GenerateDocs.sh.

Can this first file be considered a "source of truth", or is there a better place to get these type definitions from?

Script runs forever when creating group of 2 or 3 shapes/charts

Article URL

https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.worksheet?view=office-scripts#addGroup_values_

Issue

When attempting to make a new group from several pie charts or text boxes, script runs but never finishes and must be stopped manually (no errors, just runs forever).
Scenario for 3 pie charts and 1 text box.
`
function main(workbook: ExcelScript.Workbook)
{
let charts_sheet_name = "PieChartSheet";
let chart_sheet = workbook.getWorksheet(charts_sheet_name);
let shape_1 = chart_sheet.getShape("Chart 1");
let shape_2 = chart_sheet.getShape("Chart 2");
let shape_3 = chart_sheet.getShape("Chart 3");
let shape_4 = chart_sheet.getShape("Chart 4");
let text_box_1 = chart_sheet.getShape("TextBox 1");

//trying to create new group - this is where this hangs forever
let my_shapes_group = chart_sheet .addGroup([shape_1, shape_2, shape_3, shape_4, text_box_1]);
//just testing final output in b64
let new_group_image = my_shapes_group .getImageAsBase64(ExcelScript.PictureFormat.png);
console.log(new_group_image);
}
`
Expected result - new group of charts/shapes created. (then I'd return this b64 image to power automate for further processing -- no issues with that part, its just addGroup is causing issues).

What I'm missing? Any pointers/advise?

do you have method similiar to VBA PivotFields.AutoGroup？

[Enter feedback here]
Hi，is autoGroup method in VBA available in excel script?

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

Typo in TableStyle.SetName

Wrong description on TableStyle.SetName
Says "Gets the name of the table style." Should be "Sets..."

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

how to bind to an event

Hi,
it's not documented how to bind to events such as onChanged. If it's not supported, when? I find it essential to build automations....

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

fillFormats definition error

Fields >> fillFormats | Populates the adjacent cells with the selected formulas.

(a) Shouldn't "... selected formulas" be "...selected formats"?
(b) Is there a field for fillFormulas that "Populates the adjacent cells with the selected formulas."?

Thanks.

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

how to bind to events?

it's not documented how to bind to events. If it's not yet supported, when? I find it essential to build automations...

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

Document supported TypeScript version

Article URL

Office Scripts Code Editor environment

Issue

The article listed above mentions that Office Scripts are written in TypeScript, but it doesn't mention which version.
Based on some trial and error I'm fairly confident that (as of Excel version 2310) it's TypeScript v3.7, but I'd love to see some official documentation on this :)

getTextComparison example doesn't actually do anything

Copy and pasted the code directly into a new Excel sheet, saved and ran script. Did nothing to the sheet, including the cells with text in Column A starting with "Excel". Epic fail.

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

Document supported ECMAScript version

Article URL

Office Scripts Code Editor environment

Issue

The article listed above mentions that Office Scripts are written in JavaScript/TypeScript, but it doesn't mention which version.
Based on some trial and error I'm fairly confident that (as of Excel version 2310), functions up to ES2023 seem to be supported, but the typings appear to be stuck at ES2018. For example:

promise.finally(...) // ES2018, works
array.flat() // ES2019, works at runtime, but editor shows a type error
array.findLast(...) // ES2023, works at runtime, but editor shows a type error

Syntax features seem to only be supported up to TS 3.7, see #305.

This is a little annoying, but easily """fixed""" with a sinful @ts-nocheck comment at the top or through (less cursed) indexed-access (e.g. array['flat']()).
Either way, I'd love to see some official documentation on this :)

The description of the Excel.Range.setValues API is incorrect

The description of the Excel.Range.setValues is incorrect. It appears to be completely copied from the description of Excel.Range.getValues.

For example, setValues doesn't have return value, but the description states "... the returned value...".

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

Not a lot of detail here

Would be great to see an example of using this function.

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

ExcelScript.ListDataValidation.source can't take a ExcelScript.Range

When I run the following code snippet, it runs indefinitely without performing any action:

function main(workbook: ExcelScript.Workbook) {
    const rangeDataValidation = workbook.getActiveWorksheet().getRange("B2:B5").getDataValidation();
    rangeDataValidation.setRule({
        list: {
            inCellDropDown: true,
            source: workbook.getWorksheet("Data").getRange("A1:A3")
        }
    });
}

However, when I run the following code:

function main(workbook: ExcelScript.Workbook) {
    const rangeDataValidation = workbook.getActiveWorksheet().getRange("B2:B5").getDataValidation();
    rangeDataValidation.setRule({
        list: {
            inCellDropDown: true,
            source: "test1,test2,test3"
        }
    });
}

Everything works as expected. Am I doing something wrong with the cell range in the first code snippet?

(If this is not the appropriate place for this issue, please redirect me to the correct repository.)

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

Copy/Paste Values and Formatting of Visible Range Script

I would love a script example to copy/Paste Visible range including formatting and values.

I was able to create the following code for copy and pasting visible cell contents to another page, but have been unsuccessful getting the formatting from my firstSheet.

function main(workbook: ExcelScript.Workbook) {
  let firstSheet = workbook.getWorksheet("Sheet1");
  let visibleRange = `firstSheet.getRange("A1:V200").getVisibleView();`
  let copyFormatting = ExcelScript.RangeCopyType.formats;
  let visibleRangeValues = visibleRange.getValues();
  let sheetToPaste = workbook.getWorksheet("Sheet2");
  let pastedValues = sheetToPaste.getRangeByIndexes(0, 0,
    visibleRange.getRowCount(), visibleRange.getColumnCount());
  pastedValues.setValues(visibleRangeValues);

}

Any help would be appreciated.

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

What's 'inconsistent' & 'mixedCriteria' ?

Judging from the document description, I can’t understand the meaning of these two types....
I didn’t find the corresponding entry in the Excel application...
Are there examples of these two types?

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

getUsedRange throws error, not return "undefined" as in docs

The problem is with the function getUsedRange of a Range.

getUsedRange(valuesOnly) Returns the used range of the given range object. If there are no used cells within the range, then this method returns undefined.

When there is an empty range calling that function, it does not return undefined, as mentioned in the documentation, but throws an "RichApi.Error":

name: "RichApi.Error"
code: "ItemNotFound"
traceMessages: Array[0]
innerError: null
debugInfo: Object
code: "ItemNotFound"
message: "The requested resource doesn't exist."
toString: function toString()
errorLocation: "Range.getUsedRange"
statement: "var usedRange1 = range3.getUsedRange(...);"

PS. I remark another doc page on the same subject this one is correct
https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

nit: Typo in the description for parameter `fittingMode` of the `Chart.getImage` API

There seems to be a typo in the description for parameter fittingMode of the Chart.getImage API:

Optional. The method used to scale the chart to the specified to the specified dimensions (if both height and width are set).

to the specified is repeated in this sentence.

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

validation valid wholeNumber range is too small

When setting dataValidation to a whole number between -x and y with a script, x and y can be 9999, but not anything bigger than 99999. I would have expected it to be integer min and max for excel. For 64-bit OS that should be quite large. This also impacts Excel javascript API. Setting it with the Excel UI works fine.

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();
  const range = sheet.getCell(2, 16);
  range.getDataValidation().setErrorAlert({
    title: 'Invalid Data',
    message: 'The value must be a decimal number',
    style: ExcelScript.DataValidationAlertStyle.stop,
    showAlert: true,
  });
   range.getDataValidation().setRule({
      wholeNumber: {
       formula1: "0",
       formula2: "99999",
        operator: ExcelScript.DataValidationOperator.between,
      },
    });

}

Document Details

⚠ Do not edit this section. It is required for learn.microsoft.com ➟ GitHub issue linking.

Handle timeout for bulk deletion of records from excel online using script

[Enter feedback here]
Please provide a workaround for handling timeout issue related to deleting multiple records in excel online. Power Automate does not have a straightforward delete all from an excel table connector. Even the script restricts because of the timeout issue.

https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.table?view=office-scripts

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

How will we add code-snippet to Ref docs

How are we going to include code snippet examples in the reference content? It would require some manipulation to the code block to remove the error handling and Excel.run()...
Some code snippets (complex ones) may require re-write.

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.