Pasting Values to Work as Folmula (Apps Script for Google Sheets)

0
votes

I need to filter data from the latest sheet (a new one gets created every day automatically). The formula I use is 

=FILTER('S&T 18/3/2019'!N:N;ISBLANK('S&T 18/3/2019'!N:N)=FALSE)

And it works, so in another cell I have´ve written another formula that keeps the first one up to date: 

=CONCATENATE("=filter('S&T ";TEXT(TODAY();"d/m/yyyy");"'!N:N;ISBLANK('S&T";TEXT(TODAY();"d/m/yyyy");"'!N:N)=FALSE)")

In apps script I use the following code to paste the second formula as values, and it also works, but in its cell it shows as text instead of as a formula. If I manually delete the = form the beginning and then add it again it works perfectly. The idea is for it to work on its own. Can anyone help?

var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Test Filtro'), true);
    spreadsheet.getRange('D1').activate();
    spreadsheet.getRange('D1').copyTo(spreadsheet.getRange('E2'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.getRange('E2').activateAsCurrentCell();
1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

The Range#copyTo method accepts 2 optional arguments, the CopyPasteType and whether the data should be transposed. Your code fails to copy as a formula, because you use the CopyPasteType enum PASTE_VALUES. To copy a formula, you should use PASTE_FORMULA.

Enum CopyPasteType

PASTE_NORMAL    Enum    Paste values, formulas, formats and merges.
PASTE_FORMULA   Enum    Paste the formulas only.
PASTE_VALUES    Enum    Paste the values ONLY without formats, formulas or merges.

Your code would then look like:

...
var destCell = spreadsheet.getRange("E2");
spreadsheet.getRange("D1").copyTo(destCell, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
destCell.activateAsCurrentCell();
...

Since you are trying to do a "double paste" (your source data is a formula that creates a formula when it is evaluated, and you want to write the created formula), you need to paste the value of the original cell, then re-copy the output (your desired formula) and paste it as a formula:

...
const AS = SpreadsheetApp.CopyPasteType;
var destCell = spreadsheet.getRange("E2");
spreadsheet.getRange("D1").copyTo(destCell, AS.PASTE_VALUES, false); // Compute the desired formula, via formula-to-value conversion.
SpreadsheetApp.flush(); // Force the first copy to occur.
destCell.copyTo(destCell, AS.PASTE_FORMULA, false); // Activate the computed formula.
destCell.activateAsCurrentCell();
...

You may be able to avoid the first range copy entirely, by constructing the formula in script.

...
var TODAY = Utilities.formatDate(new Date(), "your timezone", "your format string here");
var myFormula = "=FILTER('S&T " + TODAY + "'!N:N;ISBLANK('S&T " + TODAY + "'!N:N)=FALSE)";
destCell.setFormula(myFormula);
...