Google Sheets/Google Apps Script Functionality vs Excel/VBA Functionality

[Violet Cartee]

My apologies if this question has been asked before, but in my research I've been unable to find anything that addresses my issue. I'm a programming consultant who has created a data entry/visualization project in Excel for one of my client's organizations, and the client is interested in having the project recreated using Google Sheets. Essentially, my issue is that as a VBA developer with fairly limited experience in JS (and none in Google Apps Script), I'm unsure if the functionality of the project could be approximated in Google Sheets. I'll try to describe the functionality in question as concisely as possible:

The purpose of the project is to visualize trends across a portfolio of up to 20 possible projects with roughly 30 data fields related to each. The user would like the option to visualize the various relationships between these projects, and with so many notable fields this obviously means that the workbook needs to have to access to (at minimum) up to 30 graphs at any given time. The main special feature of the project is that instead of in-graph legends, all of the graphs across the workbook have automatic consistent coloring associated with each project. For up to the maximum of 20 projects, the user can add/remove projects and one (currently unused) color out of 20 possible distinct high-visibility colors will automatically be associated with the project and applied to all graphs throughout the workbook. These project key colors can also be swapped for any of the other 20 possible colors at any time, provided the changes result in no duplicate color associations. For clarity, I've attached images of the data input sheet along with one of the visualization sheets below. All of this data has been randomly generated save for the producer names which I've edited for confidentiality.

Data Entry Sheet (Partial View):

One "View" of a Visualization Sheet:

While the possibility to recreate the color-related features in Google Sheets are my main area of confusion, I'm also unsure of how well many of the other design features would adapt. Since this project needs to give the user access to so many possible graphs and filters without becoming overwhelming to use, developing an adequate presentation hierarchy has been a key theme of the project. As visible in the picture of the visualization sheet, the option buttons at the top ("Views") control the combinations of graphs and slicers displayed and content displayed by the "View/Hide Additional Graphs" button is context sensitive depending on which view is selected. There's obviously the button to show/hide the key color legend as well, but that seems trivial compared to the rest of the project's programming.

Could anyone give me some insight on how much of this functionality might be able to be recreated through Google Sheets/Google Apps Script? I would be thrilled with just that, but any idea of how much work it all might take a competent developer would be wonderful to know as well.

Sorry about the long description- I tried to be as concise as possible without leaving out anything important! Any and all help would be very much appreciated!!

EDIT:

I've made a short video demonstrating the functionality of the project. The message boxes that display on confirmation or error didn't capture for some reason, but all of the main features are present in the video! This is only one of the viewing sheets planned for the project, but they'll all function in the same way.

[Andrew Apell]

Can this help? https://www.lucidchart.com/techblog/2017/12/18/how-to-build-automated-dashboards-with-google-apps-script-and-google-sheets/

[Dimu Designs]

It should be possible, but a fair amount of functionality may need to be custom built. 

For example, Google Sheets does not have native support for radio buttons, but an Apps Script savvy developer can implement them. 

However, for us as GSuite/Google Apps Script developers to effectively evaluate whether or not migrating your VBA app is feasible, we'll need to see it in action. If possible create a screen cap video walking through each and every function of the VBA app. That would provide us with more insight and allow us to gauge how much work is involved and what can realistically be migrated.

[Violet Cartee]

Oh, okay! I've made a short capture video that demonstrates all of the functional aspects of the project such including adding projects, cancelling projects, changing colors, etc. 

There are message boxes that show up to confirm any time a change is made or display an error when a duplicate color key is selected, but those didn't show up in the capture for some reason. 

Here is the link. Thank you again for your help!

[Violet Cartee]

Thank you for your reply! This specific page doesn't help much, but the website seems to have an article or two that gave some helpful general dashboard examples.

I've just edited my post to include a short video of the program if that could be helpful!

[Dimu Designs]

Based on that video I'd say migrating that tool to google sheets should be possible.

There are a few things that you'll need to be mindful of, such as the amount of data involved. Google Sheets are typically good for relatively small data-sets with a quota limit of 5,000,000 cells per workbook. If you expect your data-set to exceed that quota you may want to use a database solution that can be integrated with Google Sheets (BigQuery is a solid option as Google Sheets has native support for it, plus AppScript has a JDBC API that can connect to external databases such as MySQL and MSSQL). Optionally you could create multiple workbooks that apply to a given period (say monthly or quarterly) but I would not recommend it since managing multiple workbooks can be cumbersome.

Frankly, your project has piqued my interest and as I'm a freelance developer I'd like to take it on. If you'd like to explore that option feel free to contact me via the google form linked below:
 

https://docs.google.com/forms/d/e/1FAIpQLSfl_VOVTxZq00JwS3IMbbYYZJNtiEV52u5d3eG20aBiFwqsPg/viewform?usp=sf_link  

[Michael O'Shaughnessy]

I would also say this is "do able" in sheets.  It reminds me of running the "Query" function based of of a given cell value (change the cell value and the query updates).

Also, maybe this would be a good candidate for the new "slicers" in sheets OR maybe even a DataStudio solution....

On Tue, Oct 8, 2019 at 6:39 PM Dimu Designs <dimud...@gmail.com> wrote:

Based on that video I'd say migrating that tool to google sheets should be possible.

There are a few things that you'll need to be mindful of, such as the amount of data involved. Google Sheets are typically good for relatively small data-sets with a quota limit of 5,000,000 cells per workbook. If you expect your data-set to exceed that quota you will want to use a database solution that can be integrated with Google Sheets (BigQuery is a solid option as Google Sheets has native support for it, plus AppScript has a JDBC API that can connect to external databases such as MySQL and MSSQL). Optionally you could create multiple workbooks that apply to a given period (say monthly or quarterly) but I would not recommend it since managing multiple workbooks can be cumbersome.

Frankly, your project has piqued my interest and as I'm a freelance developer I'd like to take it on. If you'd like to explore that option feel free to contact me via the google form linked below:
 

https://docs.google.com/forms/d/e/1FAIpQLSfl_VOVTxZq00JwS3IMbbYYZJNtiEV52u5d3eG20aBiFwqsPg/viewform?usp=sf_link  

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a6084450-6cb2-47a1-aa39-31b167d2b971%40googlegroups.com.

[Violet Cartee]

Thank you for your reply! I'm quite interested to speak with you more about the possibility of developing a Sheets solution, so I sent you a request yesterday through your form :)