How to execute a Google Sheets logic within the Glide data table

Ask for Help
1

Hello everyone!

I’m trying to create an app that calculates the capacity of a BDR, a function of the sales team. I already have a Google Sheets spreadsheet that calculates this, but I would like to have an interface and I’m using Glide for that. The capacity is calculated based on the BDR’s hiring date, meaning that over time, as they learn, their capacity increases. For example, in the first month, they generate 0 opportunities, in the second month, they generate 1, etc., up to a maximum of 8 after 6 months. It’s a simple logic, and in the sheet, the following code is used: =IF(DATEDIF(B2,DATE(2023,1,31),“m”)<1,0, IF(DATEDIF(B2,DATE(2023,1,31),“m”)=1,1, IF(DATEDIF(B2,DATE(2023,1,31),“m”)=2,2, IF(DATEDIF(B2,DATE(2023,1,31),“m”)=3,3, IF(DATEDIF(B2,DATE(2023,1,31),“m”)=4,4, IF(DATEDIF(B2,DATE(2023,1,31),“m”)=5,6, IF(DATEDIF(B2,DATE(2023,1,31),“m”)>=6,8,“error”) ) ) ) ) ) ) I can use a standard Glide CRUD to add the BDRs and their hiring dates, but I can’t execute the logic afterwards. The idea is to generate a table from January to December with the capacity of each month for each BDR. Can someone shed some light on how I can do this? How can I add this logic to be executed in a column within the glide data table instead of running on the sheets? I believe this would solve the problem.

2

This will be trivial to do with Glide date math, but just so that I have correct visualisation of the desired result, could you please add a screen shot of what this looks like in your Google Sheet?

3

Hey @Darren_Murphy, thanks for the answer.

The idea is to generate a result like this for each BDR added via glide, it doesn’t necessarily have to be in a table view.

Captura de Tela 2023-04-06 às 16.55.29
Captura de Tela 2023-04-06 às 16.55.291968×898 98.8 KB

4

But would you have to add columns when it turns to 2024?

5

No @ThinhDinh . When 2024 arrives, I can only change the column names.

6

nah, you don’t want to do that. If you’re going to do that, then you may as well just write it on a piece of paper and not bother with Glide at all.

Glide is much more powerful than you think, and it’s relatively easy to make something like this fully dynamic, so that you set it up once and you’ll never have to touch it again.

Question: Do you actually need to see a calendar year of data, or would it better to see a rolling 12 month window that always starts from the current month?

1 Like
7

@Darren_Murphy This question is actually very good, the first option makes it more dynamic, as these things change constantly, but when a company makes a forecast it intends to see the annual picture from January to December. Is there a big difference in complexity between doing it both ways?

Thank you for your help.