TypeScript Function to dynamically set an entire Column range's value to eg. AD1, AD2, AD3, etc

Issue

World

I am trying to write a function that will dynamically setValues on the whole range of column AE to "AD" + i (with my assumption that i would be the incrementing value) This is the function I'm using but all it does is write AD5 into the first 5 columns, not AD1, AD2, AD3 etc...

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

I'm sure it is something simple and stupid... Thanks for any help or suggestions!

Here is the complete code:

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);
    }
}

Thanks!

Solution

Yeah, you're loop is actually writing out against all cells in the range variable so every time it goes around, it will write AD1, AD2, AD3, etc. in ALL cells.

Given you're looping 5 times, the last entry written will be AD5 and you don't even see the previous values. You can watch it and you'll see what I mean but don't blink or you'll miss it.

I've modified your script to make it do what you want but with an approach I'd be taking.

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]);
  }
}

Have a look, digest it and see if it helps.

I hope I've understood correctly.

Answered By - Skin