Google Sheet Formula gurus I need help

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

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

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:

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.

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

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.

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

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

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

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

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

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