Question

What are your favorite ways to use spreadsheets?

Mentioned
#Google Workspace #Microsoft Office 365 #Spreadsheets
Share
brianball's avatar
almost 3 years ago

I like to use spreadsheets with Zapier - collect data from Google Forms.

I created a form for questions I wanted to ask myself daily.

Each day's input gets collected into a google sheet for posterity.

6 points
maguay's avatar
@maguay (replying to @brianball )
almost 3 years ago

Love that idea—unique way of journaling!

1 point
MeeladMashaw's avatar
almost 3 years ago

My friend and I needed a way to integrate multiple API data sources with spreadsheets so we made a Google Sheets add-on using Google Apps Script and published it in the G Suite Marketplace.

4 points
maguay's avatar
@maguay (replying to @MeeladMashaw )
almost 3 years ago

Neat! What's the add-on? Does it let you query the API to fetch new data, or just automatically add new data to the spreadsheet as it comes in?

1 point
MeeladMashaw's avatar
@MeeladMashaw (replying to @maguay )
almost 3 years ago

Yes you can call an API and import new data, here's our website

1 point
kil0ran's avatar
almost 3 years ago

Discovering field sales people having affairs!

This goes way back to the days of Lotus 1-2-3 (DOS version, 3D worksheets and all that good stuff)

I used it to cross reference fuel card reimbursements and expense reports and as well as uncovering the usual expected low-grade expenses manipulation uncovered a couple of sales people both claiming for the same hotel room. That was an awkward conversation :)

More recently I've used Excel as a relational database because we weren't allowed to use Access. Huge amount of work involved, clunky as anything, and prone to errors but worked around the restriction and delivered massive results. It's the sort of thing you'd just do in Airtable these days.

4 points
maguay's avatar
@maguay (replying to @kil0ran )
almost 3 years ago

Love these historic tech stories!

Oh man, referencing stuff from other sheets can be a mess—definitely glad Airtable's around, and still find it sad Microsoft never really pushed Access further.

1 point
awwstn's avatar
@awwstn (replying to @kil0ran )
almost 3 years ago

Ha, that's hilarious!

1 point
maguay's avatar
almost 3 years ago

Here are a few of mine:

  • Creating Markdown-formatted linked lists. Say I wanted to make a list of apps and link to their Capiche profiles. I'd make a spreadsheet with the names in one column, the links in a second, then add an =concatenate formula that would first add a - [ to the beginning, then add the name from the first column followed by ](, then the link followed by ). Copy the formula to each row, then copy out the generated text. You can even generate the URLs if they're the same as the product names, which would be the case for Capiche links: Instead of adding the URL column, I'd add https://capiche.com/ followed by =lower(A1) or whatever the product name column is to generate the URL. You can use the same trick to create HTML lists or anything else repetitive—it's more tedious with HTML, but still faster than writing everything out yourself.
  • Sorting and formatting any lists. Got a text list of names, say, you want in alphabetical order? Add it to a spreadsheet and sort the column, then copy back out. Similarly you can use spreadsheet formulas such as =lower to format the text in a list of stuff.
  • Remove duplicates from a list. Paste a list into Google Sheets, then select that column and click Data -> Remove duplicates.
3 points
shoaibgm_khan's avatar
almost 3 years ago

I've used spreadsheets to build a model for our business and forecast how we will do given the current numbers and course correct sooner rather than later. Helped me and my team focus and hit our targets each time.

2 points
maguay's avatar
@maguay (replying to @shoaibgm_khan )
almost 3 years ago

That's the classic use for spreadsheets—can you imagine re-doing all those calculations by hand every time a value changed? Blows my mind thinking of all the people who worked as "computers" back in the day, calculating sheets of values by hand.

Any favorite less-known spreadsheet functions you rely on?

3 points
shoaibgm_khan's avatar
@shoaibgm_khan (replying to @maguay )
almost 3 years ago

Totally, there are bankers in my family who tell me how things used to be and how computers changed things around so much in a good way, despite their fear of otherwise. Other than that we've used google forms as our go to backend for static sites and forms like "contact us" to keep things simple.

2 points
maguay's avatar
@maguay (replying to @shoaibgm_khan )
almost 3 years ago

Google Forms is my default forms app almost solely because it can automatically save entries to Google Sheets. That's so handy.

1 point
shoaibgm_khan's avatar
@shoaibgm_khan (replying to @maguay )
almost 3 years ago

Yea totally and I just noticed my typo, we've used google sheets as our backend directly :) comes in handy.

1 point
AcTiVillain's avatar
almost 3 years ago

You can use Google sheets combined with prediction API here https://developers.google.com/apps-script/advanced/prediction and use sheets to read data and predict support case outcomes like CSAT / DSAT, how long a case may take based on the description etc.

2 points
forouzani's avatar
almost 3 years ago

Having a passion for finance, I do a lot of finance stuff in spreadsheets:

  • I track my option trades in Google Sheets. It helps me see certain numbers at a glance without having to build a complex database for it.
  • I use spreadsheets to track my credit card hacks. It's good to see when 0% interest runs out on each card.
  • Run cap rates on real estate investments. No other way to do this except on a spreadsheet.
  • Calculate business acquisition valuations - input a few numbers, and spit out some great valuations :)
2 points
saptarshinath's avatar
almost 3 years ago

I recently started playing around with Google App Scripts and created some custom formulas based on the Goodreads API, like automatically fetching the author of a book using a simple formula like =GETAUTHOR(A1). I loved the process of creating it, I wish there was an easy interface to do that though.

I wrote about the process here:

.

  • Full disclosure that Airboxr is my product (but I'm not the technical person in the team, so the real product is a lot better than my instructions here).
2 points
maguay's avatar
@maguay (replying to @saptarshinath )
almost 3 years ago

That's so cool! The built-in Google Finance queries have always made me think it'd be cool if you could pull in any structured data you want, and as an avid reader I just may have to go give that a try.

So Airboxr... makes it easier to add APIs to Sheets?

1 point
saptarshinath's avatar
@saptarshinath (replying to @maguay )
almost 3 years ago

It's still in the works but generally, the early-goal is to be able to do all your analysis without moving out of the spreadsheet to download CSVs from different systems. My cofounder speaks about it here with a cool use-case from Mailchimp:

1 point
maguay's avatar
@maguay (replying to @saptarshinath )
almost 3 years ago

That definitely would simplify a lot of workflows!

2 points
hakansogukpinar's avatar
almost 3 years ago

We have a feedback poll on Google Forms which is sent regularly to eligible users on Intercom. The responses then fall into spreadsheets raw data sheet and feed the dashboard (again on spreadsheets) to generate some graphs.

1 point
maguay's avatar
@maguay (replying to @hakansogukpinar )
almost 3 years ago

Very cool—perhaps the best thing about Google Sheets and other online spreadsheets is that they can fit into workflows like that so well, gathering data from anywhere automatically.

1 point
nickcasares's avatar
almost 3 years ago

I’ve been using Sheets with Glide to make quick data collection tools. I recently used that combo to create a light CRM for an event. It’s a great combo for lightweight data entry.

1 point
maguay's avatar
@maguay (replying to @nickcasares )
almost 3 years ago

Glide looks super nice—cool to hear it's worked well for you!

1 point
What's the best video conferencing app for internal discussions?

Three major considerations I have been using to evaluate the plethora of options available: 1. Effortless/non-intrusive: It shouldn't feel like a video call 2. Price: As this app would be complime...

Confluence alternatives for wiki/knowledge bases?

I've been looking to try something new for knowledge/documentation storage for a little while now. Confluence has always been there, but I find it to be relativly limited for the cost and additiona...

How do you manage your chat inbox?

Hey guys, first post here. As part of my work, I have to deal with and respond to a lot of incoming messages from different chats: Linkedin/WhatsApp/Signal/IG. I try to use Unreads/Archive features...

The community for power users.