Google Sheets - Apps Script Fill Down Formula (Set a Fromula & Copy Down AutoFill) Tutorial - Part 9
Вставка
- Опубліковано 26 сер 2024
- Video tutorial series about Apps Script (JavaScript) in Google Sheets (Excel VBA Equivalent).
In this tutorial we'll cover how to Fill Down a Formula (Set a Fromula & Copy Down with AutoFill) in Google Sheets using scripts. We also cover how to get last row in our data. Methods used: setFormula, getLastRow, copyTo, getRange.
Google Sheets
www.google.com...
Website:
www.chicagocom...
90% of what I know from app script I learned from you. Thank you! It has been really helpful in my job.
I don't have anything to say.... Boss!! just Salute...
This is a great tutorial. Very clear and easy to understand. Thanks for making this tutorial. I love it very much.. 😍
Now, I can add ordered number automatically when new row filled.
Awesome!
Best creator of tutorials on the internet!
Perfect. I've seen absurdly convoluted ways of doing this, but this is the fastest way so far.
I just tried this, great tutorial!, you can also fill down using the arrayformula in the formula. Works as well
👍
Finally, the information I've been looking in search of for months! Thank you so much! Can you please tell me how to have the script do that on a specific sheet (tab) that isn't the active one? I'm new to scripting, so if that doesn't make sense please tell me and I'll figure out a better way to ask.
getSheetByName("Sheet14")
instaed of
getActiveSheet()
You can change var fillDownRange = ss.getRange(lr, 4) so it will copy the formula (only) to the last row, in the case of new row addition (with proper trigger)
Super helpful thank you!
Absolutely brilliant, thank you. Google let me right to you, as I'm a subscriber I should have know to just check your channel first. Needed this today!
Awesome, thank you!
another awesome tutorial, thank you :)
Great video! Is there a way I can tell it to do the same function but change the formula and the range? Or do I have to copy and paste it each time for a different formula and range? Thanks again!
it works perfectly! also you can add others columns as you need. Tks!
Very helpfull thanks
Excellent video, do you know how I can haveit set it up so when ever I fill new range of sell the formula applies?
I do have a Main sheet (dashboard) that will create multiple tabs(sheets) using the values added by the user in column A, then I have 2 template sheets- Score sheet 1 and score sheet 2.
in score sheet 2 in L22 it is getting the value from Score sheet 1 using = '!score sheet 1'! l22, what i needed is it will automatically be added to each new tab.or even if i have a new button to replicate the formulas correctly.
Superb tutorial. Just started learning appScript from your playlist. Is there a simple way to getLastRow based on last avaialble values of a particular column. Example "B:B"
awesome tutorial - simple, clear, very useful!! thank you
Struggling with trying to write a script to paste templates on sheets based on whether checkboxes are ticked, is there a way to do it?
The logic is
If A1 is “TRUE” on sheet 1
Find an A1:B5 which is on sheet 2
Paste this A1:B5 on sheet 3
In the next blank cell after 3 in the column A
And this repeats for A1 to A10 on sheet 1
Any help is appreciated
Excellent clear video - as usual :)
Thanks for the video! Going to do a batch of these hopefully. Also, I saw that error midway through! Ha
Thanks a lot, That was awesome!
awesome tutorial
worked great
Solved it, changed line 89 to this. var fillDownRange =sheet.getRange(2,2,lr-1,7); Seems the fourth number in the getRange is the number of columns to the right of the first paste column, not the ninth column in the sheet. Also changed ss to sheet, may have had an effect. Works well now....
that's correct, it's the number of columns in the range, not the column in sheets.
Great tutorial. I put it in Speed 2 and looks like normal pace
Yea, my pace was much slower in older videos, so that makes sense.
Thanks, this is very helpful! Do you have a video for how to then copy the formula output and paste the numbers over the same range as values only?
Just record a macro and you'll have an example ua-cam.com/video/UlOGJcfpfKU/v-deo.html
Really good explanation, and i'm always trying to make a project like your video. But i make a little tweak with appendrows for the first to third column to fill. The problem is the result which mean shows in the forth column can't show automatically after A-C column fill with the appendrows, is there a way to make a result show in the forth column automatically without trigered by run button in the app script?
Great tutorials - how can one insert an automatic trigger ( onEdit perhaps) to calculate x+y=z for example on addition of a new row.
Edit- current project triggers
useful 👍👍
👍
I'm having difficulty inserting a more complex formula into this script. It keeps giving me a syntax error saying I'm missing a ")" somewhere in the formula "=If($B$4:B="",,iferror(vlookup($B$4:$B,'Recurring Expenses'!$B$1:$C$22,2,0),""))". I've got a couple other simple formulae in the same script that work fine. Any suggestions on how to resolve this issue?
Thank you so much!
Very well explained
hi, thanks for your video. how to make it work for vlookup formula from different sheet
6:20 How do you make this auto completion happens with everything in parentheses ?
I can get the auto completion but no function in the parentheses appears like yours.
I use the latest version of chrome.
Thanks for this great tutorial! Does anyone know how to keep the formula from overwriting the fill color of the cells?
Need fast result on how to change from formula to just autofill as 'Update', whenever new row added
Awesome! Thank you for this. But I have question. What if I have formula with multiple IF's linked to another sheet???
It doesn't really matter what the formula is. Maybe just use ` instead of " for JavaScript string qualifier so you don't need to escape quotes in your formula.
@@ExcelGoogleSheets Thanks. It works! 👍
👍
Thank you so much!! Been looking for a solution forever that works better than using =ARRAYFORMULA
Solid Tutorial. Is there a way to do this but do it for every other row instead of every?
Hi, all your videos are amazing and loved them!!! Is there a way to write a script to add different formulas in different columns in on edit where it doesn't refresh every time I change a cell data?
I have to be honest, I don't understand what you're trying to do.
Or we can use for loops and we have for(i=0; i
but then it depends how you are writing the data. Accessing the SS is time consuming, you won't notice it here, because it is just one time accesed, but doing it with looping you will notice.
But if you want looping (to have more controll) you can build an array of whole column in your script and then once push/acces SS.
if we applying formula which include another sheet range then?
Great video it is exactly what I need
I have error message please can you help me fix it my formula is
=ifs (c3=0,"A",c3=1,"B")
I did exactly the same code you made it in this video but it show error in the line that contains the formula.
I will grateful if you answer
use single quote around the formula setFormula('=ifs(c3=0,"A",c3=1,"B")');
@@ExcelGoogleSheets
Thank you so much it worked
Is it possible to copy down formula in multiple columns down to the last row?
Anyway..so smart ..Thank you.
Hello i have an issue while using this script with VLOOKUP. As soon as i let the script paste in my VLOOKUP it returns #NAME? - But if i copy this from C3 manualy to let's say C4, VLOOKUP will work as intended.
do you have an idea how to fix it ?
thats cool tutorial i tried to replace formula with split text,
but it said syntaxError missing ) after argument list Line 37
i dunno what to do, this is my first time, you may know the correct syntax
(Line 36) var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
(Line 37) ss.getRange("D3").setFormula("=SPLIT(C3," ; ")"));
Hello. Always thanks for your Video from S.Korea.
I try to put this formula in Java.
=if(AND(iserror(find("ABC",AC3)),iserror(find("DEF",AC3))),value(mid(AC3, FIND("(",AC3)+1,6)),"")
But it said 3rd line need ( before the factor.....
It didn't work..
What sould i do ? Can u help?
Thanks.
Thank you for this amazing video!
It is not working in my google sheet (google sheet is linked with google form). Responses and entering are recorded in the google sheet but using script suggested by you is not working in my case unless I manually make some edit in google sheet.
Here is the scenario, I understand that there will be time stamp recorded in google sheet as soon as google form response is submitted, but if a person edit that response tomorrow the time stap date changes. I want to record original date and time when that response was submitted. I think as google sheet is automatically filled by the google form your script is not working as it works as per the cell changes/edit.
Please help me if you have some script for my scenario.
Use array formulas instead or setup a trigger onFormSubmit.
Thanks My Friends!
I have a problem. How would you do so that the code is reproduced automatically while you are adding more rows with information
with onEdit?, if that's how the code would be, help!
ua-cam.com/video/eWn_JxPSbds/v-deo.html
Haloo sir, I want to autofill the formula whenever a new row is added in the last of the sheet without running the script again and again
That's what I want to do also. Any tips?
Just found the answer. Use function onEdit(e){} - developers.google.com/apps-script/guides/triggers/
Thanks!
Do you do consulting work?
How can I add a formula that has a value from another sheet in the same spreadsheet. When I set the formula it links back to the same sheet
Use ` character for javascript quotes for formula text and it should work fine.
Quick Question for you. If I run a loop, can I grab multiple values by virtue of having the i variable in the .getRange before the
.getValue as shown below.
Below is only a portion of what I wrote. So as long as i put the collection of the data in sheets (getRange.getValue) inside of a loop, shouldnt it collect all the values that it loops through? Or is the "if" the problem? because it doesn't know in which situation of many I'm talking about?
function myFunction() {
var app=SpreadsheetApp;
var sheet= app.getActiveSpreadsheet().getActiveSheet();
var lastRow= sheet.getLastRow();
for(i=4;i
Your code looks OK to me with exception that it's mission a closing brackets for your for your loop and function. I don't understand what you're asking though. You need to better explain what you're trying to accomplish.
🙏🏻
nice video
Awesome! Thank you so much for this. I've been searching for something easy like this for ages. Question though, how do I get this to only be applied to 1 of the 3 tab/sheets I have on the Spreadsheet?
I have 2 tabs (Tab/Sheet 1 = "Milestones" and Tab/ Sheet 2 = "Weekly Totals").
The script works perfectly, but it is being applied to the Row on *both* sheets, whereas I only want it to be applied to Sheet 1, "Milestones"
(not sure if it makes a difference but I am tacking on this script after another I've written that is calling out to the same Sheet)
add an if statement. if you search for my onEdit videos I have an example of that.
Maybe you can use var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Milestones");
Hello, is there a way to use app script to pull shipping status information from carriers like usps/ups/fedex etc...? is there a way to do this in google sheets? Thank u.
Should be possible if you have account with API access.
This is a great video and I am learning a lot from all of your videos. I tried this App Script with an IFS formula, but I'm getting a "missing ) after argument list" code. Any suggestions?
Share the code, it's impossible to know what you mean by description.
Thanks, I will give that a try
excelente
Sorry to bother you again
I do really need your help for my graduation project .
I want to give value equal for example to "one" two row if it is between two specific row (duplicated) .
Please help me to solve this problem and thank you again
Excellent. I have a question, how do I use this formula to copy a query formula. I keep getting error messages when I do so. Can you help me.
I want to set this formula to my first cell but keep getting error messages
=IFNA(QUERY(SEBAB!A:C,"select C where B = '"&M2&"' and A >= date '"&TEXT(A2,"yyyy-mm-dd")&"' and A < date '"&TEXT(A2+1,"yyyy-mm-dd")&"'",0),"")
use single quotes for javascript quotes '= '"&M2&"' and A >= date '"&TEXT(A2,"yyyy-mm-dd")&"' and A < date '"&TEXT(A2+1,"yyyy-mm-dd")&"'",0),"")'
Love your tutorials! Any idea how to integrate a flush & sleep to add a delay between each row processing? It works great for a web scraper we've built in gsheets, but problem is it executes for all 200+ rows at once. What would be perfect is ... C1 processes, wait 3 seconds, C2 processes, wait 3 etc. Can't get it to work however
Have you tried Utilities.sleep(1000) ?
@@ExcelGoogleSheets Yup - It waits 1000ms, then still executes all at once, rather than a 1000ms delay between each row. Thinking it needs to be redone in a loop
Try to create a function with delay and then use the function in the loop instead of Utilities.sleep(1000) directly in the loop.
You may also need to multiply the sleep amount by i to offset the timeout with each iteration.
Great tutorial, however I'm using the below formula and when I try the script with this I keep getting an error saying SyntaxError: missing ) after argument list (line 3, file "Code.gs")
=IF(NOT(ISBLANK(P8)),TODAY() - P8,"")
below is what I have in the script editor
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
ss.getRange("Q8").setFormula("=IF(NOT(ISBLANK(p8)),TODAY() - P8,"")");
var lr = ss.getLastRow();
var fillDownRange = ss.getRange(8, 17, lr-1);
ss.getRange("Q8").copyTo(fillDownRange);
}
Use single quotes for javascript to not interfere with formula quotes or better yet use back-ticks.
.setFormula('formula')
.setFormula(`formula`)
Can you set this to run only on a specific sheet - and run auto?
ua-cam.com/video/eWn_JxPSbds/v-deo.html
Very helpful in setting a "fromula" (hehe). Thank you.
Almost what I'm trying to accomplish.. I have a column of data.. starting from T4 down.. I want to enter data on T2 cell and once I press enter it copies it to the next empty cell in T column. without deleting or overwriting data already in the column. If need be create a button to run that action. I've searched and searched UA-cam and Googled it before finally commenting here.
You should be able to take the logic in this video ua-cam.com/video/548dD3iXetg/v-deo.html and modify it to do what you need.
Hi, I've been following your Script playlist and so far all have worked amazingly well which is a credit to your tuition. However, when I run this script, instead of applying the formula to my active sheet, it is applying it to the original sheet from the earlier scripts (the one with '67 High Med' etc and therefore gives an error. I know it must be something simple I've missed. Grateful for any help. Thanks for a great series!
When you write getActiveSpreadsheet() it refers to the spreadsheet where you started creating the script (aka tools->script editor). It is NOT the spreadsheet currently open in your browser. So make sure you write the code in the right spreadsheet script editor.
Learn Google Spreadsheets Thanks for the instant reply. I thought it must be something like that, in which case, how do I start a new spreadsheet & script?I tried creating a completely new spreadsheet for this tutorial but it still opened up the old scripts! Thanks again for your series and your patience.
If you do it from a new spreadsheet it should work. Try again.
Learn Google Spreadsheets Thanks. It did this time. Don’t know why it didn’t before.
How to hide the formulas in the sheet? I need only script owner to see the formulas. I created a Google sheet in which there is multiple formulas included. I want to share with my colleagues who should enter specific details in it so I gave editor access with my formulas protected only to me. But if they make a copy of my sheet all my formulas which is protected and hidden are also exposed? Can we restrict my formulas only to me, with scripts. Note : I need to give editor access too for entering specific details
Maybe use IMPORTRANGE function to move the data to a new spreadsheet where everybody else will be entering data.
I follow the instructions in the video. Very good thanks but I get this error when I run the script.
Cannot find method getRange(number,number,number,number). (line 89....
my script is
88. var lr = ss.getLastRow();
89. var fillDownRange = ss.getRange(2,2,lr-1,9);
90 . ss.getRange("B2:I2").copyTo(fillDownRange);
I have formulas programatically written into range B2:I2 then I want to copy paste those formulae down to the end of the sheet data. These formula work when I go to the spreadsheet and manually drag them down.
It seems my system does not recognise the function getRange.
Any ideas?
you may have chosen the wrong getRange, you need to make sure it's getRange(row, column, numRows, numColumns)
i am using getLastRow but it returns the last row of spreadsheet.. and that is 1000 which is blank.. actually there is blank all the row after row no. 18... but result still 1000 for getLastRow
ua-cam.com/video/zWcNzLbJpb0/v-deo.html
@@ExcelGoogleSheets thank you master
Very good tutorial. One question, I've got a problem with the autocomplete methods, when I start writting a method it doesn't recommend anything (Minute 3:01 example getRange). Do you know how to enable this function?
What web browser are you on? Try Firefox or Chrome.
@@ExcelGoogleSheets I'm currently using Brave, based on Chromium, just like Google Chrome. I will try it on Chrome now. Let me know if there is a possibility to use it on Brave too please.
if anyone encounter 'Missing ) argument list' error
- solution to this is to insert character \ as its used to "escape" the following character which allows the " to be considered as part of the string instead of closing string out
Hi, I think you can help me in my problem! Please read my comment above.
Hi Master. I'm having difficulties in adding formula with "". Example .setformula("=if(A2="","",weeknum(A2)"
What should be the final formula? First of use single quotes for javascript quotes and double quotes for formula quotes. Next make sure your formula is accurate.
How to set a trigger to perform automatic?
ua-cam.com/video/eWn_JxPSbds/v-deo.html
How about =IFS"("P3=Pending", "Red",P3="Complete","Green")... function to be add, since "" used for condition and function, unable to run..
use single quotes for javavascript quotes and you'll be fine.
@@ExcelGoogleSheets Perfectly worked well with condition changed to single quotes. But one more request. My spreadsheet was Form data. How to activate auto run the the script?
set triggers developers.google.com/apps-script/guides/triggers/installable Managing triggers manually
what do I do if a formula has a String inside like an importrange?
example: sheet.getRange(1,1).setFormula("importrange("docs.google.com/spreadsheets/d/....";"Sheet1!A:A")")
the thing is that it doesnt understand the string inside the string, im thinking of splitting them or maybe import the data some other way.
any help is appreciated :)
Use single quotes for javascript quotes.
this only works on simple formulas? what if your formula on the sheet is an array?
If it's an array then there is no need to drag it down.
@@ExcelGoogleSheets i mean how do i autofill?
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
ss.getRange("j2").setFormulas('=ARRAYFORMULA(if(MAX(IF(R[0]C[-9]=TBSS2020!R1C1:R9725C1,IF((TBSS2020!R1C2:R9725C2
@@mictralif7515 mic tralif
Highlighted reply
mic tralif
25 minutes ago
@Learn Google Spreadsheets i mean how do i autofill?
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
ss.getRange("j2").setFormula('=ARRAYFORMULA(if(MAX(IF(R[0]C[-9]=TBSS2020!R1C1:R9725C1,IF((TBSS2020!R1C2:R9725C2
Hello! Great tutorials!! I would to ask for some specific questions, dou you have any email or similar where I can send you a direct question? Thank u in advance
somebody knows how to save data from a form into another sheet, like a data sheet where I can save a sale, for example
Fher use this
function SendTheDailyJobNumbers() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('PrepSheet'), true);
spreadsheet.getRange('add range').activate();
// *** Have to figure out how to make the target a different document!!!! ***
var target = SpreadsheetApp.openById("Add Your Sheet ID here");
/*
Next we need to pick the particular sheets within those spreadsheets.
Let's say your row is on the sheet named "prepsheet", and you have a sheet in the target spreadsheet named "D2D Daily Job Numbers".
*/
var source_sheet = spreadsheet.getSheetByName("PrepSheet");
var target_sheet = target.getSheetByName("D2D Daily Job Numbers");
// The below makes the highlighted cells the range that will be copied.
var source_range = source_sheet.getActiveRange();
var last_row = target_sheet.getLastRow();
var values = source_range.getValues();
target_sheet.getRange(last_row + 1, 1, values.length, values[0].length).setValues(values);
spreadsheet.getRange('A2').activate();
}
i love u
#Feedback
Is there a way for you to create a button to run that function? I think so. It would be more interesting if you start your videos showing us the functions working first then you teach us. It would be more interesting.
Thank your for all your video lessons!
That's something I've covered in many videos. You can see an example of that by the end of this video ua-cam.com/video/ZcNmur6xiX4/v-deo.html
Hey, there is a way to create a button to run your script.
First, create a button, you can do this by clicking Insert -> Drawings, then create a shape! once you click save and close, a shape will pop up.
Next, you can assign the script to the shape. Click the shape and then the three dots on the upper right of the shape then click assign script, in the propt box enter the name of the function (to run this video's example you would enter: myFunction).
Hope that helps
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
ss.getRange("D2").setFormula("=A2+B2");
var lr = 100000;
var fillDownRange = ss.getRange(2, 4, lr-1);
ss.getRange("D2").copyTo(fillDownRange);
Yeah, doesn't work. Nothing seems to work reliably on google sheets
Not sure what you mean. It's been working fine for years.
Dont feel obligated to answer Ive already got so much from the videos
how to do in this type of formula "=IF(A2="","",IF(A2="Hotel Arizona Inn",DATA1!$D$2,IF(A2="Tulsi Motel",DATA1!$D$3,IF(A2="Dev Motel",DATA1!$D$4,IF(A2="Ravi Residency",DATA1!$D$5,"")))))"
Just use single quotes for javascript and put the formula in '=IF(A2="","",IF(A2="Hotel Arizona Inn",DATA1!$D$2,IF(A2="Tulsi
Motel",DATA1!$D$3,IF(A2="Dev Motel",DATA1!$D$4,IF(A2="Ravi
Residency",DATA1!$D$5,"")))))'
@@ExcelGoogleSheetsthank you
Struggling with trying to write a script to paste templates on sheets based on whether checkboxes are ticked, is there a way to do it?
The logic is
If A1 is “TRUE” on sheet 1
Find an A1:B5 which is on sheet 2
Paste this A1:B5 on sheet 3
In the next blank cell after 3 in the column A
And this repeats for A1 to A10 on sheet 1
Any help is appreciated