Office Scripts in Excel: Can I have a button in worksheet 1 that updates a cell in worksheet 2?

[bcsteeve]

This may be trivial, this may be impossible.  I was only made aware of the existence of Office Scripts yesterday and I really have no idea of their capability.  I was trying to look through some samples but I did not come across quite what I'm looking for.  Before diving further in, I want to ask if this is even possible.  If so, and you can further help with the "how", that would be appreciated.

I have a multi-sheet excel workbook.  On "Sheet 1" I have data brought in from an external (non-Excel) source which exports a CSV.  This gets updated daily, so every day I download the new data and paste it into Sheet 1.

On Sheet 2 I have the results of that manipulated data from Sheet 1.  I copy that and paste it back to the external source once I'm satisfied with it.

What I would like to do, additionally, is have a button that I can click that replaces a specific cell in Sheet 1 with the contents of a specific cell in Sheet 2.

The reason is because the source data only updates overnight, but in the meanwhile I'd like to keep track of what I'm doing in the day.  There's a "days since updated" cell that, at best, shows "yesterday" and never shows "today".  If I modify the data in Sheet 1, it can show "today", but I want that to be a simple click of a button on Sheet 2.

Bonus:  if that same button can save the cell's content to the clip board, it would save the user a keystroke.

Note:  We are locked out of using VBA (or else this would have been trivial).

Thank you.

[#2 · Accepted solution]

Copying a value from one cell to another is quite simple. This copies the value of cell A1 on Sheet1 to cell E1 on Sheet2:

function main(workbook: ExcelScript.Workbook) {

    workbook.getWorksheet("Sheet2").getRange("E1").setValue(workbook.getWorksheet("Sheet1").getRange("A1").getValue());

}

[#3]

Very good, thank you Jan.

[#4]

Now, how to do that if I don't know E1 is E1?

let's say I have two columns on Sheet2:  ID | Data

1 | This

2 | That

3 | The other

And I have another cell that references which ID we're working with (let's say $F$4).

How do I reference the correct cell (E1 in your code) so that it points to the row in Sheet 2 where the first column (ID) matches the value shown in $F$4?

Thank you.

[#5]

You could record a script where you do a Find for an ID in just column A of Sheet2. That should give you code you can adapt to your needs

[#6]

I had thought you try that, but a) when I used find, the macro stopped recording and b) find what? Find require that I know what I'm searching for. "Adapt to your needs" is the problem I'm having.

[#7]

@bcsteeve Perhaps this gets you started?

	let ws:ExcelScript.Worksheet = workbook.getWorksheet("Sheet1");
	let rng: ExcelScript.Range = ws.getRange("A:A")
	let fnd: ExcelScript.Range = rng.find("2", {completeMatch: true, matchCase: false, searchDirection: ExcelScript.SearchDirection.forward});
	console.log(fnd.getOffsetRange(0, 1).getValue());

Replace the "2" with whatever string it needs to look for.