Google Sheets - Apps Script Fill Down Formula (Set a Fromula & Copy Down AutoFill) Tutorial - Part 9
Why Stack Overflow recommends it
Stack Overflow users link it as a step-by-step reference when asking or answering how to auto-fill formulas into new rows with Apps Script.
Recommended by 1 people across 1 different months, 2018 to 2021.
What it is
A short screencast tutorial from a Google Sheets and Excel training channel showing how to use Apps Script to set a formula in one cell and fill it down a column. It covers getRange, setFormula, getLastRow and copyTo to find the last row of data and copy the formula down.
What people said
Recommends Johnny Cardel · 2018-09 · Stack Overflow · on “Auto fill new rows with formula using script editor works for one formula but not another”
I am using the script editor in google sheets to auto-fill a formula into new rows created by an app. My problem is that this works with one formula, but another causes an error, even though both formulas work correctly if pasted into the cell itself. All I have done is changing the formula. I am a beginner at this and have followed this tutorial: [this video] THIS WORKS function AUTOFILLLINK() { var ss3 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); ss3. getRange("AD2").setFormula("=(F2&G2&E2)"); var lr3 = ss3. getLastRow(); var filldownrange3 = ss3.getRange(2, 30, lr3-1); ss3. getRange("AD2").copyTo(filldownrange3); } THIS ONE DOES NOT WORK - it returns the following error: Missing )
Cites player0 · 2020-12 · Stack Overflow · on “consecutive grouped weighted avereages and sums in a trade log”
QUERY does not support the weighted average which means you can't have one cell ARRAYFORMULA so here is the dragging solution: AVERAGE.WEIGHTED =ARRAYFORMULA(IFNA(AVERAGE.WEIGHTED( INDIRECT("B"&ROW()&":B"&MATCH(INDEX( {IF(INDIRECT("A2:A"&MAX(IF(A$2:A="",,ROW(A$2:A))))= INDIRECT("A1:A"&MAX(IF(A$2:A="",,ROW(A$2:A)))-1),, 1); 1}, ROW()-(ROW(A$2)-1)), QUERY( {IF(INDIRECT("A2:A"&MAX(IF(A$2:A="",,ROW(A$2:A))))= INDIRECT("A1:A"&MAX(IF(A$2:A="",,ROW(A$2:A)))-1),, 1); 1}, "offset "&ROW()-1), 0)+ROW()-1), INDIRECT("C"&ROW()&":C"&MATCH(INDEX( {IF(INDIRECT("A2:A"&MAX(IF(A$2:A="",,ROW(A$2:A))))= INDIRECT("A1:A"&MAX(IF(A$2:A="",,ROW(A$2:A)))-1),, 1); 1}, ROW()-(ROW(A$2)-1)), QUERY( {IF(INDIRECT("A2:A"&MAX
Cites user14398375 · 2021-01 · Stack Overflow · on “Google Apps Script - can't set formula with concat”
I made an application with google drive and sheets api the problem is that the sheet has some formulas that has conflicts with the created api so I was forced to erase the formulas and write a little app script to autofill the formulas. I was following this [tutorial][1]. But as the formula has strings to concat it throws a syntax error. My code is here: function myFunction() { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); ss.getRange("I2").setFormula(CONCAT("problem string";"\n";C2;"\n";F2)); } Do i need to declare the string as a variable? EDIT: i am also having problems when using the \n new line character [1]: [this video]
Cites Kristkun · 2021-01 · Stack Overflow · on “Spreadsheets API - appending values when the spreadsheet has formulas in some columns”
Here are the useful references: Overview [LINK] , Bound Script [LINK] , onOpen trigger [LINK] , Spreadsheet Service [LINK] . Just a quick background, you can create functions which will include formula in a specified range in your Sheets that could be triggered/called when you open the document. [this video]