CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formula
rtenha
rtenha

Reputation: 3616

Pasting Values Only For Certain Formulas

I have a large workbook that pulls in weekly data (columns) for hundreds of metrics on several tabs. It pulls this data in via SUMIFS formulas, and on most tabs there are several rows that contain ratios/rates calculated from these SUMIFS formulas.

Here is a toy example. enter image description here

For each sheet, I would like to paste values only for formulas that are based on 'SUMIFS', and leave the other calculations. I was able to select a range and loop cell-by-cell to accomplish this, but it takes a long time due to the size of the workbook. Is there a way to do this at once in a batch-fashion? Basically, I want to copy and paste-values only if a certain condition exists.

Upvotes: 0

Views: 95

Answers (1)

Marios
Marios

Reputation: 27350

Solution:

Updated - Slight improvement to make it faster by Iamblichus.

I am not quite sure what you have tried so far, but this one works relatively fast for a couple of sheets:

function myFunction() {

let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheets = ['Sheet1','Sheet2'].map(sh=>ss.getSheetByName(sh));  
 
sheets.forEach(sh=>{ 
 let range = sh.getDataRange();
 let formula = range.getFormulasR1C1();
 let values = range.getValues();
 formula.forEach( (fr,fx) => 
 {fr.forEach((fc,fy)=>{
 let outR=sh.getRange(fx+1,fy+1);
 if (fc.toUpperCase().includes('SUMIFS')) outR.setValue(values[fx][fy]);
 })})});

}

Please adjust ['Sheet1','Sheet2','Sheet3'] to your needs.

Explanation:

I am iterating through each sheet and I am checking whether the cells contain SUMIFS formula or not. If they do, I overwrite them with their value, otherwise I keep their formula.

Upvotes: 2

Related Questions