Office Script (TypeScript) code to advance one cell every time the code runs

Issue

Coding Geniuses

I am pulling images from power automate into excel using the following Script code:

function main(workbook: ExcelScript.Workbook, sheetName: string, address: string,     base64ImageString: string) {
  let sheet = workbook.getWorksheet(sheetName);
  let range = sheet.getRange(address);
  let OldDevSNimg = sheet.addImage(base64ImageString);

  for (let i = 0; i < 3; i++) {

    let nextcell = range.getOffsetRange(+1, 0);

    OldDevSNimg.setTop(nextcell.getTop());
    OldDevSNimg.setLeft(nextcell.getLeft());
    OldDevSNimg.setWidth(300);
    OldDevSNimg.setHeight(400);
    OldDevSNimg.setLockAspectRatio(true);
    OldDevSNimg.setPlacement;
    OldDevSNimg.incrementTop(3);
    OldDevSNimg.incrementLeft(5);

  }
}

The Power Automate flow is sending a column with multiple images from a sharepoint list which need to be put on their respective rows… The issue I’m having is each image comes across and is placed on top of one another.

I am struggling with a for loop or some way to dynamically change to the next row (in the same column) for each image coming across from power automate.

Any help would be greatly appreciated! Apologies as I am new to TypeScript and just can’t get the right syntax or expression.

Thanks in advance!

Solution

This small example will start from cell A3 and keep offsetting down to the next row for 5 loops …

function main(workbook: ExcelScript.Workbook)
{
  let worksheet = workbook.getActiveWorksheet();

  for(var i = 1; i <= 5; i++) {   
    var nextCell = worksheet.getCell(2, 0).getOffsetRange(i, 0);
    nextCell.setFormula("=ROW()");
  }
}

getOffsetRange is moving from A3 by the amount of rows during each loop.

So this line of yours …

let nextcell = range.getOffsetRange(+1, 0);

… really needs to factor in the iteration from your for loop (i.e. the value of i).

It should be more like this …

let nextcell = range.getOffsetRange(i, 0);

… and your for loop should start from 1, not 0 …

for (let i = 1; i <= 3; i++)

… something like that.

Answered By – Skin

Answer Checked By – Pedro (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.