Did You Learn to Code in VBA or Google Sheets?
Not sure if anyone else has experience like this.
I learned years ago to code by coding VBA in Excel. There was a bunch of people who needed to give me info and they wrote it in excel docs. I compiled all those docs into one excel with some VBA and checked for updates once an hour. Instead of having to check myself and copy paste changes.
Then a couple years after that I learned to write scripts in Google Sheets.
I'm not a pro developer. But it did lead me to be able to write my own websites in html and css. And when learning Javascript, I had almost learned it... already.
Any challenges you faced if you took this path?
Any roadblocks you got into?
I'm now teaching a little tiny bit of coding in my google sheet tutorials: Better Sheets.
Not going to get into scripting too much. Wanted to know the pitfalls others face so I can help others learn faster and better.
VBA is exactly how I started oh so long ago. The ease of the "record macro" button made it tough to transition to starting with a blank slate when working with a code editor. I started with Python because it's readable and flexible (data work & web dev) but I found understanding the logic of programming from VBA made it simple to do basic things in JS as well.
I think the type of person digging into your Better Sheets tutorials would like to know the power of scripting and since that now has a record macro functionality, it's probably good to include in your advance stuff.
The biggest hurdle to overcome imo is not knowing what you don't know, so explaining how solutions come to be will surely be helpful.
I do some light scripting in Better Sheets. Introduction to how to get to the script writing, some math. And then we end with making a daily email update from a cell's value. Which is probably the coolest thing you have to make yourself that doesn't exist. Once someone sets up a daily email, with triggers and a cell's value.. they feel pretty darn powerful!
I bet, very nice!
VBA is really old. I did some in my previous company. Eek.
I also did Google script writing, too. It's vanilla javascript. Coming from ES6 back to vanilla is not that nice to me. Also, Google app script has timeout a lot. Meaning if your script operates on a large file, you have to watch the execution carefully. So it is hard to write scripts that scale with the file size. But it could be handy to automate the small light-weight GSheet tasks. Agree?
Unfortunately as a dev, I just don't enjoy pushing all the logic into Excel / Google Sheets. Pretty fragile and hard to test. Speaking of testing, how do you test your app script?
I would rather write something that ingests the spreadsheets and code the logic properly in other languages, e.g., Python. I blog about my experience of building Excel Importer API, if you are interested.
I understand it has value for people who live on spreadsheets in certain industry, e.g., finance. Also, I like the fact that your site is leveraging all the GSheet features.
Keep up the good work!
Yes absolutely agree. I feel like at one point, about 2 years into scripting in google sheets, I really needed to "graduate".
Yes incredibly hard to test. and hard to account for almost an infinite amount of issues. Not as simple to maintain as code which you write yourself is.
What did you find was the most helpful scripts you wrote in google sheets when you were starting?
On top of my head, I wrote a script to highlight all duplicate rows. Users loved it because it saves tons of their manual work.
Finding duplicates is definitely a major sticking point in google sheets.
I solve it by a little match formula and conditional formatting. Script may be able to extend it further. Did you run the script on every edit though?
Nope. Only run the script on a selected range as we need it.