Script for Autofill of Formula in column

Ask for Help
1

Somebody help me to get script in google sheets for autofill of formula in a column.

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DUE');
  ss.getRange("G2").setFormula("=if(D:D=false, C2*1, C2*0)");
  
  var lr=ss.getLastRow();
  var fillDownRange = ss.getRange(2, 7, lr-1);
  ss.getRange("G2").copyTo(fillDownRange);

Above script is working well, but i want to change in formula ,i.e.

 ss.getRange("H2").setFormula("=if(D:D=false, "Not Paid", " Paid")");

but this is making error, kindly suggest any other substitute formula

Thanks in Advance

2

Use “” before every double-quote.

3

Sorry, its not working. :cry:

4

It seems like you could achieve the same thing using ARRAY FORMULAS. Apart from that you can try replacing " with ’ (single quote) OR use “”" (3 quotes) OR /" (slash quote)

Those are the most coming escapes.

5

Autofill column = arrayformula for sure.

6

The corresponding formula to set it H2 should be:

=ARRAYFORMULA(IF(D:D<>"",IF(D:D=FALSE,“Not paid”,“Paid”),""))

I added a condition to filter out empty rows in D to not get your app up to 500 rows in case you are using a free version.

Please reply back if you need further help.

7

Thanks @Naos_Wilbrink your '(single quote) saved my day. :smile:

1 Like
8

Wouldn’t if be easier to just create an If/then column in Glide? Or is this a sheet that’s not used in Glide or you need to see the values in the google sheet? In that case an arrayformula would be much simpler than a script. If it’s working, don’t worry about. Just want to make sure you’re aware of all your options.

2 Likes