0

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!

EPStudio
  • 47
  • 6

1 Answers1

0

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.

Skin
  • 9,085
  • 2
  • 13
  • 29
  • Wow! Thanks Skin, I really appreciate your help! So, I now have a handle on the for loop (putting the images in a descending cell works) but the script still executes in parallel... (Power Automate is sending more than one image from an apply to each action which sends all items in the list to that one Script) is there a way to interrupt the .addImage call so that it puts one image at a time? "let OldDevSNimg = sheet.addImage(base64ImageString);" – EPStudio Feb 21 '22 at 20:12
  • Change the settings on the for loop action so it doesn’t run concurrently. Loops are configured by default to run in parallel. Also, can you flag it as the answer if you’re happy with my solution? It helps others and rewards those who help. Thanks – Skin Feb 21 '22 at 20:26
  • 1
    Ok, I will have a look to see how to change the loop settings, didn't even know that was a thing!! Thanks again for being awesome, can't tell you how long I struggled with this one! – EPStudio Feb 21 '22 at 23:19
  • No worries, if you need help on the settings part, let me know. – Skin Feb 21 '22 at 23:29
  • Well, I have gotten nowhere with changing the loop settings... What exactly is the process for that? Is it code related? Thanks... sorry such a noob * facepalm* – EPStudio Feb 21 '22 at 23:59
  • Search for "Critical Setting" in this post ... https://stackoverflow.com/questions/70858151/how-do-i-join-or-combine-the-results-of-a-foreach-loop-into-an-array/70861830#70861830 ... the settings can be found by clicking the three dots in the top right hand corner of the DO UNTIL (or whatever it's called) action. – Skin Feb 22 '22 at 00:11
  • I have solved my main issue another way! But I need your help with one more function and I feel dumb that I can't figure this out * ugh *. All I needed to do was provide a reference column that included the name of the cell offset in my 1st script. Now, I am trying to write a function that will dynamically setValues on the whole range of column AE to "AD" + i.. This is the function I'm using but all it does is write AD5 into the first 5 columns.... for (let i =2; i <=5; i++) { // Set A1 to 2. let rowID = range.setValue("AD" + i); – EPStudio Feb 22 '22 at 01:12
  • You may want to open a new question. Will make it easier to answer. Sorry to do it to you but that's probably what you need to do. – Skin Feb 22 '22 at 01:24
  • Of Course! Not a problem :) – EPStudio Feb 22 '22 at 01:36
  • Here's the link to my question: https://stackoverflow.com/questions/71214996/typescript-function-to-dynamically-set-an-entire-column-ranges-value-to-eg-ad1 – EPStudio Feb 22 '22 at 01:44