Google Scripts: paste and expand formula only on specific sheets
The situation: You have a workbook with variuos sheets which have dates as name, formatted in a '20200101' fashion. You need to replace and expand a certain formula in all these sheets. Luckily, the parameters and the rules of each sheet are the same so you do not need extra error handling.
function insertToEverySheet() {
var ws = SpreadsheetApp.getActive().getSheets();
for (i in ws) {
if (isNumeric(ws[i].getName())) {
//if (isNumeric(ws[i].getName()) & (parseFloat(ws[i].getName())) > 20200701 )
//log a set specific value only if sheet date is numeric
Logger.log(ws[i].name + ' Sheet is numerical format')
var r = ws[i].getRange('C1:C22');
var vals = r.getValues(); //loop will not work if these are empty cells;
for(var z =0;z<vals.length;z++){ws[i].getRange(z+1,3).setValue('=33+22');} //same rowindex, columnindex
ws[i].getRange('A10').setValue(10)
}
//these actio will be performed on every sheet regardless
//since they are outside of the loop.
ws[i].getRange('A1').setValue(1)
ws[i].getRange('A2').setValue(2)
ws[i].getRange('c3').setValue('=A1+A2')
}
}
//---
function isNumeric(value) {
return /^\d+$/.test(value);
}
Well not really..
function testOnOne() {
var ws = SpreadsheetApp.getActiveSheet()
var r = ws.getRange('A2:B6');
var myrng = ws.getRange('C2:C6');
ws.getRange(2,3).setValue('=A2+B2');
ws.getRange('C2').copyTo(myrng);
}
Now it adjusts the formula accordingly.
