Excel VBA to Google Sheets Script

[Madraykin]

Hi All,

So I built this cool VBA coded heat map in Excel BUT...... having moved to a new role they do most of their work in Google Sheets so they can be collaborative.

Problem is I need to recreate my heat map but, obviously, Google Sheets does not rely on VBA (it's Javascript instead isn't it???)

I know this isn't strictly an Excel question, but does anyone know how I can transfer this function into Google Sheets and have the same effect? (I can do all the background work such as naming objects)

Sub Paint()  Dim i As Integer
  For i = 1 To 380
    Range("actOrder").Value = i
    ActiveSheet.Shapes(Range("actShape").Value).Fill.ForeColor.RGB = _
    Range(Range("actColour").Value).Interior.Color


  Next i
  
End Sub

If anyone needs any background I followed an online tutorial to do this - I know you guys aren't keen on external links here, but if you google 'dynamic geographic heat map' it's currently the top result, on a site called vertex42.com

Equally if anyone can think of an easier/better way to do this so that an Excel document can be stored online and updated regularly let me know!

Sorry if slightly off topic - I didn't know where else to go as other forums are always so demeaning of me and you guys are always so lovely and helpful!

Mads

 

[Norie]

Mads

What type of shapes are you referring to in the code?

 

[Madraykin]

Hi Norie,

I have a map of the UK with all the 400+ local authority districts as separate images, they have all been individually named in Excel (I'm assuming there is a similar possibility in Sheets)?

Thanks for the reply!

 

[Norie]

Mads

I'm not sure you manipulate shapes/images in Google Sheets as you can in Excel.

You can 'insert' images and drawings but I can't seem to find a way to reference them in Google Script, well I haven't found anything so far - I'll have another look later.

PS Can you post a link to the tutorial for the heat map?

 

[Madraykin]

Hi Norie,

Really appreciate you looking into this for me - may have to just bite the bullet and not have it in Google Sheets (less work for me lol)

Here's the tutorial I followed https://www.vertex42.com/blog/help/excel-help/dynamic-geographic-heat-map-in-excel.html

Thanks again and look forward to hearing if you crack it!

Mads