Office Scripts: setting value for selected cells

[dougmarkham]

Hi Folks,

I have been asked to generate an online excel spreadsheet and automate some functions that I would normally use VBA for.
I've read a bit of intro material, but Office Script seems alien to my brain right now.

How would one replicate the following VBA code?

Sub setvalue()

Dim ws As Worksheet: Set ws = Worksheets("Sheet1")

ws.Activate

Selection.Value = "Yes"

End Sub

I'm trying to understand how the syntax works, but this doesn't seem to be covered in the introductory stuff, so any help would be greatly appreciated!

Kind regards,

Doug.

 

[b_gonzalez]

I think the equivalent code would look something like this:
function main(workbook: ExcelScript.Workbook) { let ws = workbook.getWorksheet('Sheet1'); ws.activate() workbook.getSelectedRange().setValue("Yes") }

 

[dougmarkham]

Hi *b_gonzalez,

Thanks for replying.
I've tried your script and it ran with an error: Line 4: Workbook getSelectedRange: The current selection is invalid for this operation.

Not deterred, I tried suing getSelectedRanges and setValues

I got the error: Line 5: selectedRanges.setValues is not a function.

Not sure why it's saying the function isn't valid.

Any ideas?

Kind regards,

Doug.

 

[b_gonzalez]

For discontinuous ranges like this, you have to use getSelectedRanges(). Get selected ranges returns a rangeAreas object. You access that by using the getAreas() method which returns a range array. From there, you can iterate through the array and use the setValue method like in the earlier example. The updated code would look something like this:

function main(workbook: ExcelScript.Workbook) {
  let ws = workbook.getWorksheet('Sheet1');
  ws.activate()
  workbook.getSelectedRanges().getAreas().forEach(e=>e.setValue("Yes"))
}

 

[dougmarkham]

Hi *b_gonzalez,

Awesome! That works well Thanks for your help.

Just wondering if you know of any books or resources to help learn Office Scripts, as it's quite different from VBA, but looks to be the future?

Kind regards,

Doug.

 

[b_gonzalez]

Sure you're welcome.

In terms of resources here are a few places you can look into:

Microsoft Learn for Office Scripts: Office Scripts in Excel - Office Scripts

Office Scripts page on StackOverflow. The questions are followed / answered by some of the Office Scripts dev team at Microsoft. I participate a lot on there as well: Newest 'office-scripts' Questions

Outside of those, good resources for learning Office Scripts don't really exist yet. But that will probably change some time later this year as more people start picking it up.

 

[dougmarkham]

Sure you're welcome.

In terms of resources here are a few places you can look into:

Microsoft Learn for Office Scripts: Office Scripts in Excel - Office Scripts

Office Scripts page on StackOverflow. The questions are followed / answered by some of the Office Scripts dev team at Microsoft. I participate a lot on there as well: Newest 'office-scripts' Questions

Outside of those, good resources for learning Office Scripts don't really exist yet. But that will probably change some time later this year as more people start picking it up.

Hi b_gonzalez,

Thanks so much for that!
I've got an interest in this now: forgotten how it felt not to know anything about VBA and how much fun the journey was of slowly picking up how it works.

I used a small office script to insert 'today's date' in the active cell and assigned the script to a button. I was curious at how different the syntax looks. Pipped my interest.

Was surprised about one thing though: when pressing the button, a run-time popup message comes up and there was a delay of several seconds before the script started. Running the same script from the edit window was much quicker. I wonder if there is a way to quell the popup?

I think I'll make some small projects to get going and have some time each day to get my head around the language---so thanks again for the info sources