This google apps (Javascript) formula works perfectly to move data into regional tabs. Is there any way to bring over the formulas and data validation into the new sheets? Column 1-18 are all formulas, and currently only populate with values. I'd like to keep the formulas for column 1-18 and data validation for the remaining columns.
function createSheets() {
const ss = SpreadsheetApp.getActive()
const ssh = ss.getSheetByName("Forecast (SQL) Validation");
const regions = ssh.getRange(3, 24, ssh.getLastRow() - 2, 1).getValues().flat();
const urA = [...new Set(regions)];
const shnames = ss.getSheets().map(s => s.getName())
let ws;
urA.forEach(region => {
let idx = shnames.indexOf(region);
if (~idx) {
ss.deleteSheet(ss.getSheetByName(shnames[idx]));//if it does exist delete it and create a new one
}//if it does not exist create a new one
ws = null;
if(region.length>0)
{
ws = ss.insertSheet("R"+region.split("-")[0]);
ws.getRange("A2").setFormula(`=FILTER('Forecast (SQL) Validation'!A3:CR,'Forecast (SQL) Validation'!X3:X="${region}")`)
ssh.getRange("A2:CR2").copyTo(ws.getRange("A1:CR1"))
ws.hideColumns(2,2)
ws.hideColumns(6,11)
}
})
}
I notice you are using the copyTo method, it should work and give you both formula and values:
Copies the data from a range of cells to another range of cells. By default both the values and formatting are copied, but this can be overridden using advanced arguments. It should work as suggested over your code.
I am using some of the discussion references that seem to be related to your issue. The solution to this error is to pass an appropriate object, this thread has a great discussion about the matter and a reference on using "contetnsOnlyTrue"
Edit idea that can be done to it based on those threads:
ws.getRange("A2").setFormulasR1C1(`=FILTER('Forecast (SQL) Validation'!A3:CR,'Forecast (SQL) Validation'!X3:X="${region}")`) //adding the formulasr1c1
ssh.getRange("A2:CR2").copyTo(ws.getRange("A1:CR1"), {contentsOnly:true}) //contentsOnly