Creating a TypeScript function that can dynamically assign values to a range of cells within a column, such as AD1, AD2, AD3, and so on

Hello there

I'm currently working on a function that will dynamically assign values to the column range of AE to "AD" + i. However, when I use the function provided below, it only writes AD5 into the first 5 columns instead of AD1, AD2, AD3, and so on...

for (let i =1; i <=5; i++) { 
    // Set AE2 to AE5. 
    let rowID = range.setValue("AD" + i);
}

I know there is probably a simple solution to this issue. Any help or suggestions would be greatly appreciated!

Below is the complete code for reference:

function main(workbook: ExcelScript.Workbook, sheetName: string, address: string, base64ImageString: string) {
    let sheet = workbook.getWorksheet(sheetName);
    let range = sheet.getRange("AE2:AE5");
    
    for (let i =2; i <=5; i++) {
        // Set AE2 to AE5.
        let rowID = range.setValue("AD" + i);
    }
}

Thank you in advance!

exceltypescriptoffice-scripts

Answer №1

Indeed, your loop is cycling through all cells in the range variable, resulting in the sequential writing of AD1, AD2, AD3, etc. to ALL cells each time it loops.

Since you are looping 5 times, the final entry will be AD5, overshadowing any previous values written. If you observe closely, you'll notice this rapid succession, almost like a blur if you blink.

To align with your desired outcome, I've made some adjustments to your script using an alternative approach that I personally prefer.

function main(workbook: ExcelScript.Workbook, sheetName: string, address: string, base64ImageString: string) {
  let sheet = workbook.getWorksheet(sheetName);
  let range = sheet.getRange("AE2:AE5");
  let rowCount = range.getRowCount();

  for (var row = 0; row < rowCount; row++) {
    let cellAddress = range.getCell(row, 0).getAddress();
    sheet.getRange(cellAddress).setValue(cellAddress.split('!')[1]);
  }
}

Please review the changes and see if they provide the desired outcome.

I hope I have interpreted your needs correctly.

Answer №2

It's possible that you've misinterpreted the concept behind using range.setValue

  1. By utilizing this method, every cell within the range will be filled with the value from your last iteration
  2. If you intend to achieve a specific goal, consider traversing each cell individually and setting its value instead of the entire range

Answer №3

In previous instances, I achieved this by inputting the code below into cell A1 and then pulling it down:

="AE"&row()

Continue dragging as required and utilize copy paste.special values when necessary. The idea of utilizing VBA never crossed my mind.

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

Angular progress bar with dynamic behavior during asynchronous start and stop

Currently, I am facing an issue with the progress bar functionality while utilizing the ng-bootstrap module. The scenario involves a dropdown menu with multiple options, and my desired behavior includes: The ability to start/stop the progress bar indepen ...

angulartypescriptangular8rxjs6

Exploring ways to display all filtered chips in Angular

As a new developer working on an existing codebase, my current task involves displaying all the chips inside a card when a specific chip is selected from the Chip List. However, I'm struggling to modify the code to achieve this functionality. Any help ...

htmlangulartypescriptmultidimensional-arrayangular-material

How to access the audio element in Angular using ViewChild: Can it be treated as an

My goal is to include an audio element inside a component. Initially, I approached this by using traditional methods: $player: HTMLAudioElement; ... ngOnInit() { this.$player = document.getElementById('stream') } However, I wanted to follow T ...

angulartypescriptviewchild

NextJS Typescript Layout is throwing errors due to the absence of required props

After following the instructions on https://nextjs.org/docs/basic-features/layouts#with-typescript and making changes to my Home page as well as _app.tsx, I encountered an issue with the layout file Layout.tsx. The provided guide did not include an exampl ...

reactjstypescriptnext.js

Unable to declare a string enum in TypeScript because string is not compatible

enum Animal { animal1 = 'animal1', animal2 = 'animal2', animal3 = 'animal3', animal4 = 'animal4', animal5 = 'animal5' } const species: Animal = 'animal' + num Why does typescr ...

javascripttypescript

What is the method for storing a JSON object path in a variable for use in a template?

Trying to fetch data from a lengthy path has proven challenging for me. I attempted to store the path in a variable and incorporate it into the template, but encountered some issues. Could someone assist me with this? Here is what I have tried: My store ...

typescriptangular5mustache

Why do callbacks in Typescript fail to compile when their arguments don't match?

In my current project, I encountered a scenario where a React callback led to a contrived example. interface A { a: string b: string } interface B { a: string b: string c: string } function foo(fn: (a: A) => void, a: A) { fn( ...

typescript

What is the best way to implement switchMap when dealing with a login form submission?

Is there a better way to prevent multiple submissions of a login form using the switchMap operator? I've attempted to utilize subjects without success. Below is my current code. import { Subject } from 'rxjs'; import { Component, Output } ...

javascriptangulartypescriptrxjs

Ensure that child components' property types are enforced in TypeScript

I am trying to enforce the type of a property in a child component. I expected the code below not to compile because Child's property name is not correctly typed inside Parent within the app. However, there is no compiler warning displayed. Is there ...

reactjstypescript

Choosing a value type within an interface or object declaration

Is it possible to extract a nested type object from an interface or parent type? interface IFake { button: { height: { dense: number; standard: number; }; }; otherStuff: string; } type Button = Pick<IFake, 'button'& ...

typescript

Disable and grey out the button while waiting for the Observable to broadcast successfully

component.html <button mat-raised-button color="primary" type="submit"> <mat-icon>account_box</mat-icon> <span *ngIf="!loading">&nbsp;&nbsp;&nbsp;Register</span> <span * ...

htmlangulartypescript

What are the benefits of using one state in React with useState compared to having multiple states?

Is it more beneficial to optimize and enhance code readability in React using Hooks and Functional components by utilizing a single setState hook or having multiple hooks per component? To further elaborate, let's consider the following: When workin ...

javascriptreactjstypescriptreact-hooks

Issue with Angular: Child component not receiving data after successful parent component call

I'm currently working with a parent and child component setup. Within the child component, I have a button configured like this: //child.component.html <button mat-raised-button [disabled]="!form.valid || submitButtonDisable" type = 'Submi ...

angulartypescriptbuttonparent-childngrx

Step-by-step guide to developing an Angular 2+ component and publishing it on npm

I need assistance with creating an AngularX (2+) component and getting it published on npm. My objective is to publish a modal component I developed in my current Angular App, though currently, I am focusing on creating a <hello-world> component. It ...

angulartypescriptnpm

Using RxJs in an Angular 2 application to enable row selection in a table by detecting mouse movements

Check out this example of an Angular 2 application with row selection in a table: https://plnkr.co/edit/HdQnWqbg9HloWb4eYGHz. The row selection functionality is implemented using mouse event handlers (mousedown, mousemove, mouseup). Below is the template ...

typescriptangularmouseeventrxjs

regex execution and testing exhibiting inconsistent behavior

The regex I am using has some named groups and it seems to match perfectly fine when tested in isolation, but for some reason, it does not work as expected within my running application environment. Below is the regex code that works everywhere except in ...

javascriptnode.jsregextypescript

Utilizing Angular 4 to dynamically render a template stored in a string variable

Is it possible to dynamically render HTML using a string variable in Angular4? sample.component.ts let stringTemplate = "<div><p>I should be rendered as a HTML<br></p></div>"; The contents of the sample.component.html s ...

angulartypescriptrendering

Tips for adding an extensive collection of fixed attributes to a function using Typescript

I am attempting to write a function that includes a lengthy list of static strings attached as properties, all returning the property as a string value: const arr = ["a", "b", "c"]; // there are around 140 items in the actual list const f = (tag: strin ...

typescript

Utilizing TypeScript with Svelte Components

I've been struggling to implement <svelte:component /> with Typescript without success. Here's my current attempt: Presentation.svelte <script lang="ts"> export let slides; </script> {#each slides as slide} & ...

typescriptsveltesveltekitsvelte-component

extracting the value of an option from a form control to utilize in the component's model

I'm currently facing an issue where I am unable to retrieve the value of an option selection in my component class for further processing. Despite setting the value as [value]="unit" in the view, it still shows up as undefined when passed through onMo ...

angulartypescriptangular2-ngmodel