CODEWITHSUNDEEP
if-statementgoogle-apps-scriptgoogle-sheets
Jeff Abrahams
Jeff Abrahams

Reputation: 61

How can I calculate a new date conditionally based on other information?

I have a Google Sheet which is being populated by a Google Form. I am using Google Apps Script to add some extra functionality. Please feel free to access and modify these as needed in order to help.

Based on answers from the Form, I need to return a new date that factors in the time stamp at form submission.

This is a dumbed down example of what I need to do, but let's think of it like ordering a new car and its color determines how long it is going to take.

Car Color
Toyota Red
Honda Blue
Tesla Green

I need to write a conditional IF statement that determines how many weeks it will take to get the car based on the ordered color.

- Red Blue Green
Toyota 1 3 5
Honda 2 4 6
Tesla 1 1 1

So if you order a Toyota in Red, it will take one week. If you order a Toyota in Green, it will take 5 weeks. If you order a Tesla, it will be really in one week no matter what color. Etc...

I started by writing some language in Sheets to take the Timestamp which is in Column A and add the appropriate amount of time to that:

=IFS(AND(B2 = "Toyota",C2 = "Red"),A2 + 7,AND(B2="Toyota",C2="Blue"), A2 + 21,AND(B2="Toyota",C2="Green"), A2 + 35,AND(B2 = "Honda",C2 = "Red"),A2 + 14,AND(B2="Honda",C2="Blue"), A2 + 28,AND(B2="Honda",C2="Green"), A2 + 42,AND(B2 = "Tesla"),A2 + 7)

And then I dragged that down the length of the entire column so that it would fill in as submissions came in.

enter image description here

However when you fill in the Google Form, it will overwrite what's in that entire row, blowing out what I had in that column.

Now I realized that the code needs to be written in Google Apps Script and returned as a value.

What kinds of modifications need to be made to my IFS statement in order to make it compatible with Google Apps Script?

Upvotes: 1

Views: 53

Answers (1)

NightEye
NightEye

Reputation: 11204

For easier approach, QUERY would actually solve your issue without doing script as Broly mentioned in the comment. An approach you can try is to create a new sheet. Then have that sheet contain this formula on A1

Formula (A1):

=query('Form Responses 1'!A:C)

This will copy A:C range from the form responses, and then, copy/paste your formula for column Date Needed on column D.

Output:

output

Note:

  • Since you only copied A:C, it won't affect column D formula.
  • Your A:C in new sheet will update automatically, then the formula you inserted on D will recalculate once they are populated.
  • Add IFNA on your formula for column D to not show #N/A if A:C is still blank.

Formula (D2):

=IFNA(IFS(AND(B2 = "Toyota",C2 = "Red"),A2 + 7,AND(B2="Toyota",C2="Blue"), A2 + 21,AND(B2="Toyota",C2="Green"), A2 + 35,AND(B2 = "Honda",C2 = "Red"),A2 + 14,AND(B2="Honda",C2="Blue"), A2 + 28,AND(B2="Honda",C2="Green"), A2 + 42,AND(B2 = "Tesla"),A2 + 7), "")

Upvotes: 1

Related Questions