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!

CodePudding user response：

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.

CodePudding user response：

you may Misunderstood the meaning of this method range.setValue

1. when you call the method, it will fill all the range, so all cells are the value of your last traversal
2. if you want to implement your purpose you could Traverse all cells and set the cell, not the range