Is there a way to reuse logic that appends a row to a sheet while traversing a column of values?

Question

I am creating an athlete training program and diary for athletes I coach. I created a sheet in which I set workloads of different items, and athletes record how much of the training plan they complete.

They then hit a save button at the top which transfers the data to a different spreadsheet which stores their training data.

The main page the athletes fill in has 4 sections, each with 10 rows that have drop down boxes to set the different training activities.

I have a code which is working, but is going to be very lengthy. Below is the code. Currently I will have to have 10 lots of the listed code (2 listed), for each of the 4 areas of training. This means 250 lines per section so 1000 lines for the whole code.

Seeing as I am just changing the if statement down 1 row at a time, and then all of the input ranges change accordingly, is there a way to shorten this code right down?

//Input values for routines data row 1
var routinesTRPDMT = inputS.getRange("A29").getValues(); 
if (routinesTRPDMT == "TRP"){
var routines = [[inputS.getRange("R1").getValue(),//DAY
inputS.getRange("V1").getValue(),//DATE
inputS.getRange("Z1").getValue(),//MONTH
inputS.getRange("AD1").getValue(),//SESSION
inputS.getRange("B29").getValue(),//ROUTINE
inputS.getRange("D29").getValue(),//SECTION
inputS.getRange("F29").getValue(),//REPS
inputS.getRange("J29").getValue(),//ATTEMPT 1
inputS.getRange("L29").getValue(),//ATTEMPT 2
inputS.getRange("N29").getValue(),//ATTEMPT 3
inputS.getRange("P29").getValue(),//ATTEMPT 4
inputS.getRange("R29").getValue(),//ATTEMPT 5
inputS.getRange("T29").getValue(),//ATTEMPT 6
inputS.getRange("V29").getValue(),//ATTEMPT 7
inputS.getRange("X29").getValue(),//ATTEMPT 8
inputS.getRange("Z29").getValue(),//ATTEMPT 9
inputS.getRange("AB29").getValue(),//ATTEMPT 10
inputS.getRange("AD29").getValue(),//ATTEMPT 11
inputS.getRange("AF29").getValue(),//ATTEMPT 12
dtCurrentTime]];
              
routinesS.getRange(routinesS.getLastRow()+1, 1, 1, 20).setValues(routines);
               
}   

//Input values for routines data row 2
var routinesTRPDMT = inputS.getRange("A30").getValues(); 
if (routinesTRPDMT == "TRP"){
var routines = [[inputS.getRange("R1").getValue(),//DAY
inputS.getRange("V1").getValue(),//DATE
inputS.getRange("Z1").getValue(),//MONTH
inputS.getRange("AD1").getValue(),//SESSION
inputS.getRange("B30").getValue(),//ROUTINE
inputS.getRange("D30").getValue(),//SECTION
inputS.getRange("F30").getValue(),//REPS
inputS.getRange("J30").getValue(),//ATTEMPT 1
inputS.getRange("L30").getValue(),//ATTEMPT 2
inputS.getRange("N30").getValue(),//ATTEMPT 3
inputS.getRange("P30").getValue(),//ATTEMPT 4
inputS.getRange("R30").getValue(),//ATTEMPT 5
inputS.getRange("T30").getValue(),//ATTEMPT 6
inputS.getRange("V30").getValue(),//ATTEMPT 7
inputS.getRange("X30").getValue(),//ATTEMPT 8
inputS.getRange("Z30").getValue(),//ATTEMPT 9
inputS.getRange("AB30").getValue(),//ATTEMPT 10
inputS.getRange("AD30").getValue(),//ATTEMPT 11
inputS.getRange("AF30").getValue(),//ATTEMPT 12
dtCurrentTime]];
              
routinesS.getRange(routinesS.getLastRow()+1, 1, 1, 20).setValues(routines);
               
}

sheet structure

What language/program is this being developed in? [Edit] to tag that, instead of the irrelevant tags you currently have, please. — underscore_d, Jul 13 '20 at 14:54

Oleg Valter is with Ukraine · Answer 1 · 2020-07-16T03:45:09.467

Treat your code less as a set of instructions and more like an alogrithm. In your case, the following steps are taken to reach the goal:

Traverse the column A, for each [ cell ], do:
1. If [ cell value ] is "TRP", then:
  1. Get 4 values from row 1, columns "R", "V", "Z", "AD"
  2. Get 15 values from the row in the [ row that matches the cell ]
  3. Merge 1 and 2 into a row, add timestamp and append to [ sheet ]

You may have noticed that:

Step 1 in the condition has constant values, hence it can be reused out of the loop
"cell" and "matching row" vary each iteration, hence they may become parameters

Collections of items are traversed by using a loop, code reuse is achieved with functions.

Below is a utility function for mapping the initial data to the desired output. You can use it in a loop to traverse the "A:A" column's values acquired with getValues() (you do not have to get a single cell value each time, just expand the range). How you want it to be connected up to you:

const mapRoutine = (rowIdx, constants, rows, callback) => {
  const row = rows[rowIdx];  

  const [ 
    , routine ,, section ,, reps ,,,, 
    a1 ,, a2 ,, a3 ,, a4 , ...restAttempts
  ] = row;
  
  //skip odd indices columns starting from attempt 1 (i.e. !(0 % 2) = !0 => true )
  const attempts5to12 = restAttempts.filter((a,i) => !(i % 2));

  const values = [ routine,section,reps,a1,a2,a3,a4, ...attempts5to12 ];
  
  callback([ 
    ...constants.slice(0,-1), 
    ...values, 
    ...constants.slice(-1) 
  ]);
}

//testing:

const rowIndex = 0;
const constants = [1,2,3,4,5];
const firstRow = [,"R",,"S",,"RP",,,,"1",,"2",,"3",,"4","5",,"6",,"7",,"8",,"9",,"10",,"11",,"12"];

mapRoutine(
  rowIndex, 
  constants,
  [ firstRow ], 
  console.log 
);

The signature of the utility above is simple except for the last parameter, which is a function - since functions in JavaScript are first-class objects, and thus can be passed around as parameters just like any other object.

In your case, the callback might look something like this (note the currying in the last line):

const sheet = //get sheet somehow;
const appendRow = (sheet) => (row) => sheet.appendRow(row);
const curriedAppend = appendRow(sheet); //still a function

You need to enable the newer V8 runtime for the above to work

Rob · Answer 2 · 2020-07-14T04:10:08.013

You seem to have a pretty unique case set and how your spreadsheet could be set up a bit easier to do what I'm going to suggest, but it will cut down on your code considerably:

var date = inputS.getRange("V1").getValue(),//DATE
var month = inputS.getRange("Z1").getValue(),//MONTH
var session = inputS.getRange("AD1").getValue(),//SESSION
var routines = inputS.getRange(29,1,10,32).getValues(); //get the whole range

for (var i = 0; i< routines.length; i++) {
  var row = routines[i];
  
  if(row[0] == 'TRP') {
  var array = [date,month,session];
  for (var j = 0; j< row.length; j++) {
        if(j == 6) {
            j = j + 3; //because there are 3 blank columns
        } else if (j >9) {
            j++; //because every other cell is hidden past column J
        }
        array.push(row[j])
     }
  routineS.appendRow(array)
  }
}

Is there a way to reuse logic that appends a row to a sheet while traversing a column of values?

2 Answers2