Retrieve the final data-filled cell in a specified column

In Excel Office Scripts, I'm trying to find a way to dynamically select a range starting from cell B2 all the way down to the last cell in column 2 that contains data without any gaps.

Basically, I want to achieve the same result as manually selecting B2 and pressing Ctrl + Down in Excel.

Does anyone know of a method that can accomplish this task?

Here is the code I'm currently using, and I'm looking to replace .getRange("B2:B4") with an automated solution.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Set font bold to true for dynamically selected range starting from B2 on selectedSheet
    // Need help with this part
}
typescriptoffice-scripts

Answer №1

Give it a shot.

function main(workbook: ExcelScript.Workbook) {
    let activeSheet = workbook.getActiveWorksheet();
    
    // Retrieve continuous data range
    let dataRange = activeSheet.getRange("B2").getExtendedRange(ExcelScript.KeyboardDirection.down);
    console.log(dataRange.getAddress());
    dataRange.getFormat().getFont().setBold(true);

    // Retrieve non-continuous data range
    let lastCell = activeSheet.getRange("B:B").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex();
    let dataRange2 = activeSheet.getRangeByIndexes(1,1,lastCell,1)
    console.log(dataRange2.getAddress());
    dataRange2.getFormat().getFont().setBold(true);
}

For more information, refer to Microsoft documentation:

getExtendedRange(direction, activeCell)

getRangeEdge(direction, activeCell)

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Tips for changing the color of an MUI 5 checkbox and label when hovering

I am looking to create a checkbox enclosed in a wrapper with a label. The goal is to change the color of everything inside the wrapper when it is hovered over. Here is an example image: https://i.sstatic.net/T3OU5.png Below is the code I have attempted: ...

reactjstypescriptmaterial-uireact-tsxcss-in-js

Simulation service agent partnered with openApi backend

I am currently utilizing MSW along with the OpenAPI-backend package for my project. I aim to mock both the browser server and test server using OpenAPI specifications. With an available OpenAPI definition, I generate `generated.ts` for RTK Query (which is ...

reactjstypescriptopenapireact-testing-librarymsw

What is the best way to extract the primary base64 value from reader.result?

After successfully retrieving the base64 value of my file, I noticed that along with the value, I am also getting the type of file and the type of string. However, I only require the actual value in order to send it to the backend. Code for converting fil ...

javascriptangulartypescriptbase64

What causes the index to display [object Object] rather than an integer in React?

It has been a long time since I last worked with React, and now I'm facing an issue. Whenever I use console.log to display the index within the map function, my console output looks like this: https://i.stack.imgur.com/VbGmE.png However, the result ...

javascriptreactjstypescriptloopsnext.js

What are the steps to create custom Typescript RecursiveOmit and RecursivePick declarations for efficient cloning routines?

For some time now, I have been attempting to create a declaration for RecursiveOmit and RecursivePick in cloning methods such as JSON.parse(JSON.stringify(obj, ['myProperty'])) type RecursiveKey<T> = T extends object ? keyof T | RecursiveKe ...

javascripttypescripttypescript-typingstypescript-generics

The parameter in the Typescript function is not compatible with the generic type

What causes func1 to behave as expected while func2 results in an error? type AnyObj = Record<string, any>; type Data = { a: number; b: string }; type DataFunction = (arg: AnyObj) => any; const func1: DataFunction = () => {}; const arg1: Data ...

typescriptgenerics

What is the significance of having both nulls in vue's ref<HTMLButtonElement | null>(null)?

Can you explain the significance of these null values in a vue ref? const submitButton = ref<HTMLButtonElement | null>(null); ...

typescriptvue.js

What should be included in the types field of package.json for TypeScript libraries?

I'm finding it challenging to efficiently develop multiple typescript modules simultaneously with code navigation while ensuring the correct publishing method. What should I include in the "types" field of my package.json? Referring to: Typescriptlan ...

typescriptvisual-studio-code

Utilizing a loaded variable containing data from an external API request within the useEffect() hook of a React component

Essentially, I have an API request within the useEffect() hook to fetch all "notebooks" before the page renders, allowing me to display them. useEffect(() => { getIdToken().then((idToken) => { const data = getAllNotebooks(idToken); ...

reactjstypescriptnext.jsreact-hooks

Using Node with Express and TypeScript to serialize date types as epoch milliseconds universally

I am looking to update my node-based API backend to serialize all Date types as Epoch milliseconds instead of ISO format. For instance, if I have the following interface: export interface Profile { updatedAt: Date; updatedBy: string; } And when I ...

node.jstypescriptexpress

There seems to be an issue with the Angular QuickStart project as it is not functioning properly, showing the error message "(

After following the instructions in this guide for setting up VS2015, I encountered issues when trying to run the "quick start" project or the "tour of heroes" tutorial on Google Chrome. The error message I received can be found here: Angular_QuickStart_Er ...

htmlangulargoogle-chrometypescriptvisual-studio-2015

Compilation in TypeScript taking longer than 12 seconds

Is anyone else experiencing slow TypeScript compilation times when building an Angular 2 app with webpack and TypeScript? My build process takes around 12 seconds, which seems excessively slow due to the TypeScript compilation. I've tried using both ...

performancetypescriptangularwebpack

Working with multiple arguments in typescript

As a newcomer to TypeScript, I am looking to understand how to pass multiple parameters in a TypeScript context. In JavaScript, for example, we would typically do the following: const createOrUpdate = async (data = {name, email, password}) =>{ const ...

typescriptamazon-dynamodbuser-input

Error in Typescript stating that the property 'children' is not found on the imported interface of type 'IntrinsicAttributes & Props'

When I try to import an interface into my Card component and extend CardProps, a yarn build (Typescript 4.5.4) displays the following error: Type error: Type '{ children: Element[]; className: string; border: true; disabled: boolean; }' is not as ...

typescript

I'm getting errors from TypeScript when trying to use pnpm - what's going

I've been facing an issue while attempting to transition from yarn to pnpm. I haven't experimented with changing the hoisting settings yet, as I'd prefer not to do so if possible. The problem lies in my lack of understanding about why this m ...

typescriptmaterial-uiyarnpkgpnpm

Angular 2 date validation rule for dd/mm/yyyy format in forms with reactive functionality

this.seedFundForm = this.fb.group({ multipleSource: this.fb.array([]), amount:[data.amount, Validators.compose([Validators.required, Validators.pattern('[0-9]*'), Validators.maxLength(10)])], date:[data.date, Validators.compose([Valid ...

angulartypescript

Fetching URL from Right Before Logging Out in Angular 2 Application

I am struggling to capture the last active URL before logging a user out of my Angular 2 app. My goal is to redirect them back to the same component or page once they log back in. Currently, I am using this.router.routerState.snapshot['url'] to r ...

javascriptangulartypescriptroutes

What are the best methods for testing a function containing multiple conditional statements?

I have a complex function that I want to showcase here, it's quite simple but for some reason, I'm struggling with writing unit tests for it. I don't need the exact unit test implementation, just a general approach or tips on how to handle i ...

javascriptangulartypescriptunit-testingif-statement

How can I set the default bindLabel for a dropdown in @ng-select/ng-select when the self change event occurs in Angular

I have a scenario where I need to set the default value to null in the ng-select. If the user selects an option from the dropdown first, then on the change event it should check if the Amount model is not null or blank. If the Amount model is blank, then ...

angulartypescript

How can we optimize ternary statements within ternary statements in Type Script and React Native for best practices?

Can you help me optimize this code snippet that uses nested ternary operators for better readability and correctness? <TouchableOpacity style={ darkMode ? filterState === 'A' ? styles.activeButtonDark : styles.buttonDa ...

javascriptreactjstypescriptreact-native