Answer the question
In order to leave comments, you need to log in
How to shorten the code?
There are several sheets in the Google spreadsheet that are reduced to one, the search takes place by name and then the numbers are substituted into the columns compared with the shifts in the rows, There are more than 200 columns and the number of rows is constantly changing.
This is an incomplete version of the script:
function myF10() {
/*Калонка F10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("F10").setFormula("=IFERROR(VLOOKUP(B10;'Эта неделя'!$B$3:$E$120;3;FALSE);0)");
var ksF10 = list.getLastRow();
var diapF10 = list.getRange(10, 6, ksF10-9);
list.getRange("F10") .copyTo(diapF10) ;
/*Калонка G10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("G10").setFormula("=IFERROR(VLOOKUP(B10;'Эта неделя'!$B$3:$E$120;4;FALSE);0)");
var ksG10 = list.getLastRow();
var diapG10 = list.getRange(10, 7, ksG10-9);
list.getRange("G10") .copyTo(diapG10) ;
/*Калонка H10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("H10").setFormula("=IF(C10>=10;F10-70;(F10+G10)-70)+J10");
var ksH10 = list.getLastRow();
var diapH10 = list.getRange(10, 8, ksH10-9);
list.getRange("H10") .copyTo(diapH10) ;
/*Калонка I10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("I10").setFormula("=H10+J10");
var ksI10 = list.getLastRow();
var diapI10 = list.getRange(10, 9, ksI10-9);
list.getRange("I10") .copyTo(diapI10) ;
/*Калонка K10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("K10").setFormula("=IFERROR(VLOOKUP(B10;'Прошлая неделя'!$B$3:$E$120;3;FALSE);0)");
var ksK10 = list.getLastRow();
var diapK10 = list.getRange(10, 11, ksK10-9);
list.getRange("K10") .copyTo(diapK10) ;
/*Калонка L10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("L10").setFormula("=IFERROR(VLOOKUP(B10;'Прошлая неделя'!$B$3:$E$120;4;FALSE);0)");
var ksL10 = list.getLastRow();
var diapL10 = list.getRange(10, 12, ksL10-9);
list.getRange("L10") .copyTo(diapL10) ;
/*Калонка N10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("N10").setFormula("=IFERROR(VLOOKUP(B10;'Позапрошлая неделя'!$B$3:$E$120;3;FALSE);0)");
var ksN10 = list.getLastRow();
var diapN10 = list.getRange(10, 14, ksN10-9);
list.getRange("N10") .copyTo(diapN10) ;
/*Калонка O10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("O10").setFormula("=IFERROR(VLOOKUP(B10;'Позапрошлая неделя'!$B$3:$E$120;4;FALSE);0)");
var ksO10 = list.getLastRow();
var diapO10 = list.getRange(10, 15, ksO10-9);
list.getRange("O10") .copyTo(diapO10) ;
/*Калонка Q10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("Q10").setFormula("=IFERROR(VLOOKUP(B10;'Месяц'!$B$3:$E$120;3;FALSE);0)");
var ksQ10 = list.getLastRow();
var diapQ10 = list.getRange(10, 17, ksQ10-9);
list.getRange("Q10") .copyTo(diapQ10) ;
/*Калонка R10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("R10").setFormula("=IFERROR(VLOOKUP(B10;'Месяц'!$B$3:$E$120;4;FALSE);0)");
var ksR10 = list.getLastRow();
var diapR10 = list.getRange(10, 18, ksR10-9);
list.getRange("R10") .copyTo(diapR10) ;
/*Калонка S10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("S10").setFormula("=IF(C10>=10;F10+K10+N10+Q10;F10+G10+K10+L10+N10+O10+Q10+R10)+J10");
var ksS10 = list.getLastRow();
var diapS10 = list.getRange(10, 19, ksS10-9);
list.getRange("S10") .copyTo(diapS10) ;
/*Калонка T10*/
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
list.getRange("T10").setFormula("=IF(C10>=10;(K10-70)+J10;(K10+L10)+J10-100)");
var ksT10 = list.getLastRow();
var diapT10 = list.getRange(10, 20, ksT10-9);
list.getRange("T10") .copyTo(diapT10) ;
}
Answer the question
In order to leave comments, you need to log in
Alternatively, something like this
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var counter = 6;
var logics = {
"F10": "=IFERROR(VLOOKUP(B10;'Эта неделя'!$B$3:$E$120;3;FALSE);0)",
"G10": "=IFERROR(VLOOKUP(B10;'Эта неделя'!$B$3:$E$120;4;FALSE);0)",
"H10": "=IF(C10>=10;F10-70;(F10+G10)-70)+J10",
"I10": "=H10+J10"
}
for (var key in logics) {
formulaPaste (list, counter, key, logics[key]);
counter++;
}
function formulaPaste (list, i, cell, formula){
list.getRange(cell).setFormula(formula);
var row = list.getLastRow();
var range = list.getRange(10, i, row-9);
list.getRange(cell).copyTo(range);
}
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question