Google Sheet Formula gurus I need help

Ask for Help
1

Scenario

2 work sheets

Sheet1 Tasks

Tasks can be added and deleted

Columns

  • Name
  • Date
  • Email
  • Task Description

Sheet2 Running Total

Columns

  • Name
  • Email
  • Running Total

Help needed

Every time a new task is added in the tasks sheet

Check whether the row exists in sheets2 identified by email, if not create one.

Then increment the running total column by 1 in the running total sheet.

The task can then be deleted but the running total is preserved.

Any gurus able to provide a solution? I bet you can…

Cheers

2

Jason,
I have a way for it to be done using Zapier.

  1. Trigger a ZAP when a new row is added to Sheet 1.
  2. Find the corresponding record from Sheet 2 using a combination of Name and Email. You may have to create a helper column that combines name and email in both sheets.
  3. Update the record to increment the value of Running Total by 1.

Zapier can be task-consuming, so this may not be ideal.

I don’t think it can be done using Formulas in the Sheets. You will have to use Apps Script if you want to do it within Sheets.

1 Like
3

Thanks good suggestion. I did think about using Zapier but was wanting to avoid using 3rd party solutions :slight_smile: there must be another way :slight_smile: :grinning:

4

What if you didn’t allow actual deletions, but just add a switch to remove the task from the list. This would preserve the spreadsheet entry, but remove it from view in the app.

5

Yeah already do that thanks. Trying to preserve my rows as they are building up quickly even on my pro account. I could condense 1000 rows into 1 if I allow delete. Thanks

6

What if you used a Query to create a new sheet that only contained items that aren’t marked as deleted and use that sheet for your app instead? Just trying to think of non-Zapier or script methods for you.

7

Ideally I didn’t want to create a new sheet, just want a new row added to other sheet and increment by 1. There must be a way…I’m sure. Thanks for the suggestion though

8

I think there will be at some point.

Glide has hinted that there will be triggered action functionality in the near future. I imagine that once a new task is created, also add one to Column X could be possible. Until then, a script is your best bet.

1 Like
9

Thanks, yeah agree script is the way to go. Just got to figure it out…wish me luck (I need it!) cheers :beers:

10

Here’s one quick way of doing it with G.S. commands/formulas away from needing google scripting or other tools:

image
image1169Ă—413 26.3 KB

Formulas used in Sheet=Running:

In A2 Cell =arrayformula(if(B2:B<>"",vlookup(B2:B,Tasks!A:B,2,FALSE),""))
In B2 Cell =arrayformula(unique(Tasks!A2:A))
In C2 Cell =arrayformula(if(B2:B<>"",countif(Tasks!A:A,B2:B),""))

1 Like
11

Thank you, I am gonna try and have a go with this on Monday, wish me luck :slight_smile:

12

Sounds very possible with some code in app script. Just to make sure I understand you: User submits task name and email address to Sheet2. You want to check if that task/email combo already exists in Sheet1. If it does not, add that info from Sheet2 to Sheet1. Then, what total is it you want to increment? All tasks submitted, identical tasks submitted, or tasks per user submitted? Also, do the tasks have to be deleted? If not, then you basically can just write a function that will compare the lists in each sheet and add any new ones from Sheet2 to Sheet1 and add 1 to the running total each time one is added. I’d be glad to help you write it.

13

Thanks Justin

Does this explain it any better?

image
image2048Ă—2048 434 KB

14

It does. What exactly is the running total counting?

15

The running total of tasks

16

As far as I understand GS I think the formulae will work on the data which is present inside of the sheet only which means when you delete, there’s no way for the formulae to “remember” what got deleted.
While with scripts you could do this easily.

17

Sorry. Is it a total of all tasks or tasks per user?

18

All tasks per user, thanks