Office Script Indirect referencing

[ArchieSmith]

Hi everyone, 

I am trying to copy values from one cell to another using Office Scripts (which uses TypeScript), however these cells may change in row number and are therefore not fixed. 

Does anyone know a way that you can use the contents of one (fixed location) cell to obtain a location of another (non-fixed location) cell? this would be similar to how the 'INDIRECT()' Function works in excel. 

I have managed to make a formula in excel that outputs the non-fixed cells' grid locations with this formula:

For example this outputs 'H42' and will change if more rows are added.

I then want to use this in my copy paste script and this is the best way I thought it would work:

(the value of cell H42 which is displayed in A1 is going to be copied to cell M42 which is displayed in A2)

(ws has been defined earlier in the worksheet as the active worksheet)

However this doesn't work and the error box says the following:

If anyone knows how I would be able to do this I would be very grateful!

Thanks, 

Archie

[#2]

Hey @ArchieSmith 

(won't be able to help) If you search this site you'll find a few Office Script threads

It's my understanding that Office Script issues should be raised on StackOverflow/office-scripts

[#3]

Hi Archie,

If I understood properly, you can achieve this result with two lines of code:

let value_to_paste = ws.getRange(String(box_hval)).getValue() //assign the value from cell H42, referenced in A1 to variable value_to_paste
ws.getRange(String(box_mval)).setValue(value_to_paste) //paste the value from value_to_paste in cell M42, referenced in A2

You just replace the "ws.getRange(box_mval).copyFrom..." line for it.

Let me know if I can help you with something else :)