Add Formula When Form Submitted
Learn how to automatically add formulas to your Google Sheets when a form is submitted using Google Apps Script. This tutorial covers creating a function and setting up triggers to streamline your data processing.
We wanna add a formula when the form is submitted. Uh, form responses end up on a sheet called form responses one or form responses two. We can always rename the sheet, but we can also add a formula here by hand. We can say total cost. We can say equals this times this, and cool, we can add that. But when we add a new. Entry into here. Let's submit another response. We have three office chairs. We submit them, if we're have a column that has some kind of formula , but it's not copying down all the way, every time we enter, we need to, we add this. Every single time we add a new line. So let's do that with Extensions app script . So we're gonna do this in two parts.
We're going to create the function , and then we're going to create the trigger that connects it to the form. So first, let's create a function . ADD formula and we'll need an event here. This is a variable that holds some information about what form entries are being added and where they are. So we'll say cons. Sheet equals E source. Do GI get active sheet . Cons, row equals E range . Do get row and we'll put in a formula called sheet Do get range. We're gonna be on whatever row we're on in the sixth column. We're going to set value. We can either do set value or set formula. I like to do set value here because it will automatically put it in as Don't Delete My Formulas
a formula if we have the equal sign. So we do back ticks if D and now this is the hard part. We're gonna create curly brackets here and put a dollar sign , and then we're gonna write the word a row. We will see if less than five. Need some more. We'll put another comma because this formula , if it's false, we want it to show nothing, but if it's true, meaning it is less than five, we want it to say needs more. We'll hit save or command S. And then over on the left side triggers, this is the second part. We need to add a trigger . On the bottom right, choose which function to run, add formula , or whichever formula you name it from spreadsheet and select event type. On form, submit, click save.
May have to authorize this. Allow, and now we can see it here. If we need to delete it, go over to the three dots on the right and click delete trigger . But let's go and check it. Let's create a luxury keyboard for 99. 99. We have two of these. Hit, click, submit. We have a failure. E is not defined at line two. Probably did not. Put the whole event there. I just put E 'cause I usually do that here. Let's fix it, save it, submit another response. We have one hard drive submit and we have an error . A very simple issue. We have to put the dollar sign outside of the curly bracket, command S or save, and let's go submit another response. Our item, and now we have a correct formula here.
And it's gonna be added every single time we add a new item to our form, we fill out the Google form . Thanks for watching. Hope you enjoyed that and I hope you enjoyed the failures we got along the way to get the right functions here. are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.