What is the best way to create an Office Script autofill feature that automatically fills to the last row in Excel?

Having trouble setting up an Excel script to autofill a column only down to the final row of data, without extending further. Each table I use this script on has a different number of rows, so hardcoding the row range is not helpful. Is there a way to make the script dynamically autofill based on the actual number of rows in each table?

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.getRange("B2").autoFill("B2:B237", ExcelScript.AutoFillType.fillDefault);
}

I attempted to manually set the .autofill() range to B2:B2000, but encountered issues with filling blank rows beyond the last row that I wanted to avoid. When trying B:B, the script malfunctioned. Any advice or suggestions would be highly appreciated. Thank you!

javascriptexceltypescriptoffice-scripts

Answer №1

Give it a shot

function main(sheet: Script.Sheet) {
    let currentSheet = sheet.getActiveWorksheet();
    let lastRowUsed = currentSheet.getUsedRange().getLastCell().getRowIndex();
    currentSheet.getRange("C3").autoFill(`C3:C${lastRowUsed+1}` , Script.AutoFillType.fillSeries);
}

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

Where can I locate information on using the .get method?

Recently, I encountered a code snippet on this site that helped me resolve an issue. The individual who provided the code utilized a .GET method that was unfamiliar to me. Here's a sample snippet: If you'd like to see the complete code, you can ...

javascriptjqueryjsonajaxknockout.js

Discovering the power of VBA in combination with Selenium to pinpoint and interact with a particular cell within a table

I am attempting to access a specific cell within a table on a webpage. The cell I need to reach has an id of "flowTile_6". It is highlighted below. I have tried multiple methods, but all resulted in errors. One approach I attempted was using the followi ...

htmlexcelvbaselenium

What is the best way to find all the corners along a path?

I am working with an SVG path and I need to find all the extremum points (corners) on this path. How can I achieve this? I attempted to retrieve all points using the following code: let totalLength = path.getTotalLength(); for (var i = 0; i < totalL ...

javascriptsvg

Express Js EJS Layouts encountered an issue: No default engine was specified and no file extension was included

Hey there! I'm currently experimenting with implementing Express EJS Layouts in my application. However, as soon as I try to include app.use(expressEjsLayouts), an error is being thrown. The application functions perfectly fine without it, but I reall ...

javascriptnode.jsexpressflash

What is the proper way to implement parameters and dependency injection within a service?

My objective is to achieve the following: (function(){angular.module('app').factory("loadDataForStep",ls); ls.$inject = ['$scope','stepIndex'] function ls ($scope, stepIndex) { if ($routeParams ...

javascriptangularjsdependency-injection

"Exclusive Mui sx styles will be applied only when a specific breakpoint

As I update my old mui styling to utilize the sx attribute, I've noticed the ability to specify styles for different breakpoints using sx = {{ someCssProp: { xs: ..., md: ... and so on. Prior to this, I had been using theme.breakpoints.only for some ...

javascriptcssreactjsmaterial-uibreakpoints

What is the reason behind this being deemed as true?

Imagine we have this snippet of code: var attachRed = false; Why is attachRed = !attachRed equivalent to true? I'm curious because I'm working with Vue.js and trying to grasp why this particular piece of code functions as it does. <div id= ...

javascriptvue.js

Events in the backbone view fail to trigger after a re-render

For some reason, I am struggling to get mouse events to work on my backbone view after it is re-rendered. It seems like the only solution is to use a rather convoluted jQuery statement: $("a").die().unbind().live("mousedown",this.switchtabs); I initially ...

javascriptjquerybackbone.jsunderscore.jsbackbone-events

Acquire multiple instances of NestJs dependencies using dependency injection within a single class

Below is a class called Product @Injectable() export class Product { brand: string; } I am injecting the Product class into ProductService export class ProductService { constructor(private readonly product: Product) {} update(products) { ...

node.jstypescriptdependency-injectionnestjs

Display various child components in a React application depending on the current state

I'm currently developing a brief React quiz where each question is represented as an independent component. I aim to swap out the current question component with the next one once a question is answered. Here's the present state of my root compon ...

javascriptreactjs

A valid ReactComponent must be returned in order to properly render in React. Avoid returning undefined, an array, or any other invalid object

While working on my react application, I came across an error that I have been trying to troubleshoot without any success. It seems like I must be overlooking something important that could be quite obvious. *Error: VideoDetail.render(): A valid ReactComp ...

javascriptreactjsreact-nativereact-routerreact-redux

Resolving TypeScript error: Property 'Error' does not exist on type 'Angular2 and Objects'

One of the models I am working with is called "opcionesautocomplete.model.ts" interface IOpcionesAutocomplete { opcionesStyle: OpcionStyle; pcionPropiedades: OpcionPropiedades; } export class OpcionesAutocomplete implements IOpcionesAutocomplet ...

angulartypescriptobject

When using iOS, the video compressing process stops automatically if the screen is no longer active while the file input

I am working on a web application that includes a file upload feature for large videos, typically 30 minutes or longer in duration. When a user on an iOS device selects a video to upload, the operating system will automatically compress it before triggerin ...

javascripthtmliosiphone

Updating a dataview inside a Panel in extjs 3.4

I am facing an issue with my extjs Panel component that includes a dataview item. Initially, it works perfectly fine in displaying images from a store. However, upon reloading the imgStore with new image URLs (triggered by a user search for a different cit ...

javascriptjqueryextjsextjs3

Javascript issue: opening mail client causes page to lose focus

Looking for a solution! I'm dealing with an iPad app that runs html5 pages... one specific page requires an email to be sent which triggers the Mail program using this code var mailLink = 'mailto:' + recipientEmail +'?subject=PDFs ...

javascriptioshtmlipadmailer

Could there be an issue with my function parameters, or is there another underlying problem?

Hey there! I've been working on this function, but it doesn't seem to be running quite right. Here's the code: function chooseCols(colTag, tagName) { // Set column name var column = $('.tagChooser:eq(&apo ...

javascriptjqueryfunction

non-concurrent in Node.js and JavaScript

I'm a beginner in the world of NodeJS and I have a question that's been bugging me. Node is known for its asynchronous nature, but JavaScript itself also has asynchronous features (like setTimeout). So why weren't concepts like Promise intr ...

javascriptnode.jsasynchronous

Listener document.addEventListener (function() {perform the function as shown in the illustration})

Currently facing an issue that I'm struggling to resolve, attempting to execute a code snippet from the ngcordova website without success. While using the plugin $cordovaInAppBrowser.open ({... it generally functions well until the inclusion of the ...

javascriptangularjscordovaionic-frameworkcordova-plugins

Obtaining parameter types for functions from deeply nested types

I'm currently facing a challenge involving deeply nested parameters. When dealing with non-nested parameters, everything functions smoothly without any issues export type test = { 'fnc1': () => void, 'fnc2': () => void, ...

typescript

Using async and await for uploading images

I am trying to create a post and upload an image if one is provided. If I successfully upload the image, everything works smoothly. However, if I do not upload an image, I encounter the following error: UnhandledPromiseRejectionWarning: TypeError: Cannot r ...

javascriptexpressfile-uploadasync-await