I have written a script where users will renter their data and click a button to run the script, the script will append the data in two different tabs and at last it will clear the data entry tab. But my problem is I had to hard code each cell to append in the next sheet, due to that the script is searching even the blank rows. I do not know how to shorten my script so that it can target only the non-empty rows and append-only that to the next tab so the execution time can be minimized. I have to keep the button function as it is. Please help me out. Here is the script where data from the "Verify" tab has been appended.
function verify() {
const mainFunctionName = "verify"; // function name Mf this function.
const alartFunctionName = "alert4";
const drawings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("VERIFY").getDrawings();
const drawing = drawings.filter((e) => e.getOnAction() == mainFunctionName);
if (drawing.length == 1) {
drawing[0].setOnAction(alartFunctionName);
SpreadsheetApp.flush();
vf();
drawing[0].setOnAction(mainFunctionName);
}
}
function alert4() {
SpreadsheetApp.getUi().alert("NOW SAVING");
}
function vf() {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const pt = ss.getSheetByName("VERIFY")
const pcons = ss.getSheetByName("INDEX5")
const fg = ss.getSheetByName("INDEX6")
const pt1 = ["B1","C6","C7","C8","C9"]
const pt2 = ["B10","B11","B1","B2","B3","B4","B5","D6","D7","D8","D9","E9","F9","B12"]
const pt3 = ["M15","M16","M17","M18","M19","M20","M21","M22","M23","M24","M25","M26","M27","M28","M29","M30","M31","M32","M33","M34","M35","M36","M37","M38","M39"]
const pfv = ["A15","L15","B15","C15","D15","E15","F15","G15","H15","I15","J15","N15",]
const pfr = pfv.map(f => pt.getRange(f).getValue())
const pfv1 = ["A16","L16","B16","C16","D16","E16","F16","G16","H16","I16","J16","N16"]
const pfr1 = pfv1.map(f => pt.getRange(f).getValue())
const pfv2 = ["A17","L17","B17","C17","D17","E17","F17","G17","H17","I17","J17","N17"]
const pfr2 = pfv2.map(f => pt.getRange(f).getValue())
const pfv3 = ["A18","L18","B18","C18","D18","E18","F18","G18","H18","I18","J18","N18"]
const pfr3 = pfv3.map(f => pt.getRange(f).getValue())
const pfv4 = ["A19","L19","B19","C19","D19","E19","F19","G19","H19","I19","J19","N19"]
const pfr4 = pfv4.map(f => pt.getRange(f).getValue())
const pfv5 = ["A20","L20","B20","C20","D20","E20","F20","G20","H20","I20","J20","N20"]
const pfr5 = pfv5.map(f => pt.getRange(f).getValue())
const pfv6 = ["A21","L21","B21","C21","D21","E21","F21","G21","H21","I21","J21","N21"]
const pfr6 = pfv6.map(f => pt.getRange(f).getValue())
const pfv7 = ["A22","L22","B22","C22","D22","E22","F22","G22","H22","I22","J22","N22"]
const pfr7 = pfv7.map(f => pt.getRange(f).getValue())
const pfv8 = ["A23","L23","B23","C23","D23","E23","F23","G23","H23","I23","J23","N23"]
const pfr8 = pfv8.map(f => pt.getRange(f).getValue())
const pfv9 = ["A24","L24","B24","C24","D24","E24","F24","G24","H24","I24","J24","N24"]
const pfr9 = pfv9.map(f => pt.getRange(f).getValue())
const pfv10 = ["A25","L25","B25","C25","D25","E25","F25","G25","H25","I25","J25","N25"]
const pfr10 = pfv10.map(f => pt.getRange(f).getValue())
const pfv11 = ["A26","L26","B26","C26","D26","E26","F26","G26","H26","I26","J26","N26"]
const pfr11 = pfv11.map(f => pt.getRange(f).getValue())
const pfv12 = ["A27","L27","B27","C27","D27","E27","F27","G27","H27","I27","J27","N27"]
const pfr12 = pfv12.map(f => pt.getRange(f).getValue())
const pfv13 = ["A28","L28","B28","C28","D28","E28","F28","G28","H28","I28","J28","N28"]
const pfr13 = pfv13.map(f => pt.getRange(f).getValue())
const pfv14 = ["A29","L29","B29","C29","D29","E29","F29","G29","H29","I29","J29","N29"]
const pfr14 = pfv14.map(f => pt.getRange(f).getValue())
const pfv15 = ["A30","L30","B30","C30","D30","E30","F30","G30","H30","I30","J30","N30"]
const pfr15 = pfv15.map(f => pt.getRange(f).getValue())
const pfv16 = ["A31","L31","B31","C31","D31","E31","F31","G31","H31","I31","J31","N31"]
const pfr16 = pfv16.map(f => pt.getRange(f).getValue())
const pfv17 = ["A32","L32","B32","C32","D32","E32","F32","G32","H32","I32","J32","N32"]
const pfr17 = pfv17.map(f => pt.getRange(f).getValue())
const pfv18 = ["A33","L33","B33","C33","D33","E33","F33","G33","H33","I33","J33","N33"]
const pfr18 = pfv18.map(f => pt.getRange(f).getValue())
const pfv19 = ["A34","L34","B34","C34","D34","E34","F34","G34","H34","I34","J34","N34"]
const pfr19 = pfv19.map(f => pt.getRange(f).getValue())
const pfv20 = ["A35","L35","B35","C35","D35","E35","F35","G35","H35","I35","J35","N35"]
const pfr20 = pfv20.map(f => pt.getRange(f).getValue())
const pfv21 = ["A36","L36","B36","C36","D36","E36","F36","G36","H36","I36","J36","N36"]
const pfr21 = pfv21.map(f => pt.getRange(f).getValue())
const pfv22 = ["A37","L37","B37","C37","D37","E37","F37","G37","H37","I37","J37","N37"]
const pfr22 = pfv22.map(f => pt.getRange(f).getValue())
const pfv23 = ["A38","L38","B38","C38","D38","E38","F38","G38","H38","I38","J38","N38"]
const pfr23 = pfv23.map(f => pt.getRange(f).getValue())
const pfv24 = ["A39","L39","B39","C39","D39","E39","F39","G39","H39","I39","J39","N39"]
const pfr24 = pfv24.map(f => pt.getRange(f).getValue())
Utilities.sleep(10000);
pcons.appendRow(pfr)
pcons.appendRow(pfr1)
pcons.appendRow(pfr2)
pcons.appendRow(pfr3)
pcons.appendRow(pfr4)
pcons.appendRow(pfr5)
pcons.appendRow(pfr6)
pcons.appendRow(pfr7)
pcons.appendRow(pfr8)
pcons.appendRow(pfr9)
pcons.appendRow(pfr10)
pcons.appendRow(pfr11)
pcons.appendRow(pfr12)
pcons.appendRow(pfr13)
pcons.appendRow(pfr14)
pcons.appendRow(pfr15)
pcons.appendRow(pfr16)
pcons.appendRow(pfr17)
pcons.appendRow(pfr18)
pcons.appendRow(pfr19)
pcons.appendRow(pfr20)
pcons.appendRow(pfr21)
pcons.appendRow(pfr22)
pcons.appendRow(pfr23)
pcons.appendRow(pfr24)
const fg1 = pt2.map(f => pt.getRange(f).getValue())
fg.appendRow(fg1)
pt1.forEach(f => pt.getRange(f).clearContent())
pt3.forEach(f => pt.getRange(f).clearContent())
}
CodePudding user response：
Try it this way:
function vf() {
const ss = SpreadsheetApp.getActive()
const pt = ss.getSheetByName("VERIFY")
const pcons = ss.getSheetByName("INDEX5")
const fg = ss.getSheetByName("INDEX6")
const pt1 = ["B1", "C6", "C7", "C8", "C9"]
const pt2 = ["B10", "B11", "B1", "B2", "B3", "B4", "B5", "D6", "D7", "D8", "D9", "E9", "F9", "B12"]
const pt3 = ["M15", "M16", "M17", "M18", "M19", "M20", "M21", "M22", "M23", "M24", "M25", "M26", "M27", "M28", "M29", "M30", "M31", "M32", "M33", "M34", "M35", "M36", "M37", "M38", "M39"]
const vs = pt.getRange("A15:N39").getValues().map(([a, b, c, d, e, f, g, h, i, j, k, l, m, n]) => [a, l, b, c, d, e, f, g, h, i, j, n]);
pcons.getRange(pcons.getLastRow() 1, 1, vs.length, vs[0].length).setValues(vs);
const fg1 = pt2.map(f => pt.getRange(f).getValue())
pt1.forEach(f => pt.getRange(f).clearContent())
pt3.forEach(f => pt.getRange(f).clearContent())
}
CodePudding user response：
This may not be shorter but definitely faster.
First, you use a lot of arrays containing the A1 notation of disjointed and rearranged cells. (I'm not saying thats bad, its what you need for your work process).
Second, you then use getValue() from the same sheet many times. This is the most significant cause of any performance issue.
Many novice Google App Script programmers like to use A1 notation for ranges, whereas I typically use array indices. This utilty helps to bridge that gap.
What I do is getDataRange().getValues() of all the values from the sheet at once. Then using my utility function getIndices(cell) convert an A1 notation to row and column index. Then index into values to get the value and construct a row array.
My test sheet looks like this.
Code.gs
function testTheFunction() {
try {
let cells1 = ["B1","C2","D3"];
let cells2 = ["E3","D2","C3","B2","A3"]
let spread = SpreadsheetApp.getActiveSpreadsheet();
let values = spread.getSheetByName("Sheet1").getDataRange().getValues();
console.log(getArrayValues(cells1,values));
console.log(getArrayValues(cells2,values));
}
catch(err) {
console.log(err);
}
}
/**
* @param {string} cells[].cell - the spreadsheet cell in A1 notation
* @param {number} values[].value - the spreadsheet cell value
* @return {number} results[].value - an array of values
*/
function getArrayValues(cells,values) {
try {
let results = [];
cells.forEach( cell => {
let indices = getIndices(cell);
results.push(values[indices.row][indices.col]);
}
);
return results;
}
catch(err) {
console.log(err);
}
}
/**
* @param {string} cell - the spreadsheet cell in A1 notation
* @returns {Object} object - containing row and col
*/
function getIndices(cell) {
try {
let parts = cell.match(/[a-z] /i);
if( !parts ) throw "incorrect cell [" cell "]";
parts = parts[0];
let col = 0;
let i = 0;
while( i<parts.length ) {
let char = parts.charCodeAt(i);
col = 26*col char-64;
i ;
}
parts = cell.match(/\d /);
if( !parts ) throw "incorrect cell [" cell "]";
row = parseInt(parts[0]);
return { row: row-1, col: col-1 };
}
catch(err) {
console.log(err);
}
}
Execution log
6:46:18 AM Notice Execution started
6:46:20 AM Info [ 11, 22, 33 ]
6:46:20 AM Info [ 43, 32, 23, 12, 3 ]
6:46:20 AM Notice Execution completed
Example
function vf() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const pt = ss.getSheetByName("VERIFY");
let values = pt.getDataRange().getValues();
.
.
.
const pfv = ["A15","L15","B15","C15","D15","E15","F15","G15","H15","I15","J15","N15",];
const pfr = getArrayValues(pfv,values);
.
.
.