Code shortening for data entry form Google App script

Question

I'm new to coding, and I know I am going a long way about this and making my script run slow, but I can't figure out how to shorten and optimise it (now I have tried to map the second bit of code using Marios comment)

I have made a data entry form on Google Sheets for athletes I coach to use as a training diary. After recording training data in a session, they hit the save button and this script transfers it to a different spreadsheet with all of their training data ever in.

Below is a section of code I have attempted to shorten with Marios comment:

function submitSession1() {
workloadSubmit();
myValue();
}

function workloadSubmit(){
var inputSS       = SpreadsheetApp.getActiveSpreadsheet();
var inputS        = inputSS.getSheetByName("Session 1"); 
var outputSS      = SpreadsheetApp.openByUrl() 
var workloadS     = outputSS.getSheetByName(); 
var dtCurrentTime = new Date();

//Input Values for Workload data
var workloads = [[inputS.getRange("M1").getValue(), 
                  inputS.getRange("N1").getValue(), 
                  inputS.getRange("O1").getValue(), 
                  inputS.getRange("P1").getValue(), 
                  inputS.getRange("AK3").getValue(), 
                  inputS.getRange("AK5").getValue(), 
                  inputS.getRange("AL3").getValue(),
                  inputS.getRange("AL5").getValue(), 
                  inputS.getRange("BC3").getValue(), 
                  inputS.getRange("BC5").getValue(),
                  inputS.getRange("BD3").getValue(), 
                  inputS.getRange("BD5").getValue(), 
                  inputS.getRange("AM3").getValue(), 
                  inputS.getRange("AM5").getValue(), 
                  inputS.getRange("AN3").getValue(), 
                  inputS.getRange("AN5").getValue(),
                  inputS.getRange("AO3").getValue(),
                  inputS.getRange("AO5").getValue(),
                  inputS.getRange("AP3").getValue(),
                  inputS.getRange("AP5").getValue(),
                  inputS.getRange("AQ3").getValue(),
                  inputS.getRange("AQ5").getValue(),
                  inputS.getRange("AR3").getValue(),
                  inputS.getRange("AR5").getValue(),
                  inputS.getRange("AS3").getValue(),                
                  inputS.getRange("AS5").getValue(),
                  inputS.getRange("AT3").getValue(),
                  inputS.getRange("AT5").getValue(),
                  inputS.getRange("AU3").getValue(),
                  inputS.getRange("AU5").getValue(),
                  inputS.getRange("AV3").getValue(),
                  inputS.getRange("AV5").getValue(),
                  inputS.getRange("AW3").getValue(),
                  inputS.getRange("AW5").getValue(),
                  inputS.getRange("AX3").getValue(),
                  inputS.getRange("AX5").getValue(),
                  inputS.getRange("AY3").getValue(),
                  inputS.getRange("AY5").getValue(),
                  inputS.getRange("AZ3").getValue(),  
                  inputS.getRange("AZ5").getValue(),
                  inputS.getRange("BA3").getValue(),
                  inputS.getRange("BA5").getValue(), 
                  inputS.getRange("BB3").getValue(), 
                  inputS.getRange("BB5").getValue(), 
                  dtCurrentTime]];

                workloadS.getRange(workloadS.getLastRow()+1, 1, 1,   
                45).setValues(workloads);
}

// Drills Data Submit

function myValue(col) {
var inputSS       = SpreadsheetApp.getActiveSpreadsheet();
var inputS        = inputSS.getSheetByName("Session 1");
var outputSS      =     SpreadsheetApp.openByUrl() 
var drillsS       = outputSS.getSheetByName("Drills Data"); 
var dtCurrentTime = new Date();
return inputS.getRange(col).getValue();
}

var colns = ["M1", "N1", "O1", "P1", "A14","B14","D14","F14","G14","H14","J14","K14","L14","M14","N14","O14","P14","Q14","R14","S14","T14","U14"];

var drillsData = colns.map(myValue)
  drillsData.push(dtCurrentTime)

I am now getting the error code:

Exception: Argument cannot be null: a1Notation (line 71, file "Code")Dismiss

Any help is much appreciated

Marios · Answer 1 · 2020-07-24T15:23:50.110

1

You can calculate drillsData using maps:

function myValue(col) {
  return inputS.getRange(col).getValue();
}

var colns= ["M1", "N1", "O1", "P1", "A14","B14","D14","F14","G14","H14","J14",
"K14","L14","M14","N14","O14","P14","Q14","R14","S14","T14","U14"];

var drillsData = colns.map(myValue)
drillsData.push(dtCurrentTime)

*Don't forget to call drillsData as [drillsData].

Unfortunately, the columns you want to retrieve are not sequential, therefore selecting the full range is not an option.

Or you can create custom functions to make your code look cleaner:

function importSheets(sheetN) {
      return outputSS.getSheetByName(sheetN);
    }
var workloadS = importSheets("W.L +  Full Routine Data")

For the latter you can again create maps using the same logic described for one. As a result, you can have a collection of sheets objects as elements in an array and call by using their index.

edited Jul 24 '20 at 15:23

answered Jul 24 '20 at 15:17

Marios

26,333
8
32
52

Thanks Marios, I have tried to input this into my script, but am now getting the error code : Exception: Argument cannot be null: a1Notation (line 71, file "Code")Dismiss. I have adapted my question above to have the full code in now, can you see why this is happening ? – Elliot Stratton Jul 25 '20 at 02:27
1

Hi, I can see you have completely changed your code. The variable drillsData should be the array with the values you now entered to workloads. Please if you change the code like that you end up with errors that my code didn't predict. – Marios Jul 25 '20 at 08:13
1

Return to the original code you had and just replace drillsData with my drillsData solution . – Marios Jul 25 '20 at 08:13
Hi ! You have some errors in your code @ElliotStratton .In ```workloadSubmit``` function when you do ```getSheetByName``` in ```workloadS``` you are not inputing the actual name and therefore when you do ```workloadS.getRange``` later it will return an error as it doesn't know which sheet you are referring to. Also, I think you wrongly mixed both your old and Marios code returning something that will not work. Could you please provide a sample sheet with *no sensitive data* so that it is clearer what you are trying to achieve here? – Mateo Randwolf Jul 27 '20 at 09:04

Code shortening for data entry form Google App script

1 Answers1