CODEWITHSUNDEEP
typescriptpower-automate
EPStudio
EPStudio

Reputation: 107

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

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!

Upvotes: 0

Views: 995

Answers (1)

Skin
Skin

Reputation: 11262

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.

Upvotes: 0

Related Questions