1
\$\begingroup\$

This is really my first shot at scripting anything. What I have below works, it does exactly what I need it to, but this is only a quarter of the total function. Clearly, I need to cut down the length somehow.

This is the spreadsheet that this script is referencing.

This script is fairly simple, albeit long. What I'm trying to do is this: A user changes the drop down value in "Sign Out // Status" (D), adds a comment in the "Comments // Notes" column (F), and a date to the In-service Date (G). Once doing this, they will select the vehicle's fleet number they wrote the comment for, and the server section the comment is written in; in the top right of the sheet with the drop down menus. For example: If someone edits D6 and adds a comment and date to F6 and G6, then that vehicle fleet number would be #BJS3101 and would be for Server 1. Once they click the "Copy" button (top right as well), it will copy these values into the corresponding cells for the same vehicle fleet number in the other servers. Only the values are copied, so the formatting is left alone.

So these three values: D6, F6, and G6 (in my example) would be copied to their respective rows for the other servers. So for Server 2: D20, F20, G20; Server 3: D34, F34, G34; and Server 4: D48, F48, G48.

The portion of script I have attached is only for the server 1 vehicles. The script is essentially replicated three more times with adjustments to the ranges and such for the other three servers. It's totaling 494 lines right now, and I would very much like to cut down on that.

function copyPaste() {
  var s = SpreadsheetApp.getActiveSheet ();
  var vehicle = s.getRange("Vehicle Sign Out!G2").getValue();
  var server = s.getRange("Vehicle Sign Out!G1").getValue();

  //Server 1 ifs ----------------------------------------------------------------------------------------------

  if (vehicle == "#BJS3101" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F6:G6").copyTo(s.getRange("Vehicle Sign Out!F20:G20"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F6:G6").copyTo(s.getRange("Vehicle Sign Out!F34:G34"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F6:G6").copyTo(s.getRange("Vehicle Sign Out!F48:G48"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D6").copyTo(s.getRange("Vehicle Sign Out!D20"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D6").copyTo(s.getRange("Vehicle Sign Out!D34"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D6").copyTo(s.getRange("Vehicle Sign Out!D48"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3102" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F7:G7").copyTo(s.getRange("Vehicle Sign Out!F21:G21"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F7:G7").copyTo(s.getRange("Vehicle Sign Out!F35:G35"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F7:G7").copyTo(s.getRange("Vehicle Sign Out!F49:G49"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D7").copyTo(s.getRange("Vehicle Sign Out!D21"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D7").copyTo(s.getRange("Vehicle Sign Out!D35"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D7").copyTo(s.getRange("Vehicle Sign Out!D49"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3103" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F8:G8").copyTo(s.getRange("Vehicle Sign Out!F22:G22"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F8:G8").copyTo(s.getRange("Vehicle Sign Out!F36:G36"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F8:G8").copyTo(s.getRange("Vehicle Sign Out!F50:G50"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D8").copyTo(s.getRange("Vehicle Sign Out!D22"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D8").copyTo(s.getRange("Vehicle Sign Out!D36"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D8").copyTo(s.getRange("Vehicle Sign Out!D50"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3104" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F9:G9").copyTo(s.getRange("Vehicle Sign Out!F23:G23"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F9:G9").copyTo(s.getRange("Vehicle Sign Out!F37:G37"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F9:G9").copyTo(s.getRange("Vehicle Sign Out!F51:G51"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D9").copyTo(s.getRange("Vehicle Sign Out!D23"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D9").copyTo(s.getRange("Vehicle Sign Out!D37"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D9").copyTo(s.getRange("Vehicle Sign Out!D51"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3105" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F10:G10").copyTo(s.getRange("Vehicle Sign Out!F24:G24"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F10:G10").copyTo(s.getRange("Vehicle Sign Out!F38:G38"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F10:G10").copyTo(s.getRange("Vehicle Sign Out!F52:G52"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D10").copyTo(s.getRange("Vehicle Sign Out!D24"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D10").copyTo(s.getRange("Vehicle Sign Out!D38"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D10").copyTo(s.getRange("Vehicle Sign Out!D52"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3106" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F11:G11").copyTo(s.getRange("Vehicle Sign Out!F25:G25"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F11:G11").copyTo(s.getRange("Vehicle Sign Out!F39:G39"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F11:G11").copyTo(s.getRange("Vehicle Sign Out!F53:G53"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D11").copyTo(s.getRange("Vehicle Sign Out!D25"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D11").copyTo(s.getRange("Vehicle Sign Out!D39"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D11").copyTo(s.getRange("Vehicle Sign Out!D53"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3107" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F12:G12").copyTo(s.getRange("Vehicle Sign Out!F26:G26"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F12:G12").copyTo(s.getRange("Vehicle Sign Out!F40:G40"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F12:G12").copyTo(s.getRange("Vehicle Sign Out!F54:G54"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D12").copyTo(s.getRange("Vehicle Sign Out!D26"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D12").copyTo(s.getRange("Vehicle Sign Out!D40"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D12").copyTo(s.getRange("Vehicle Sign Out!D54"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3108" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F13:G13").copyTo(s.getRange("Vehicle Sign Out!F27:G27"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F13:G13").copyTo(s.getRange("Vehicle Sign Out!F41:G41"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F13:G13").copyTo(s.getRange("Vehicle Sign Out!F55:G55"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D13").copyTo(s.getRange("Vehicle Sign Out!D27"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D13").copyTo(s.getRange("Vehicle Sign Out!D41"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D13").copyTo(s.getRange("Vehicle Sign Out!D55"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3109" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F14:G14").copyTo(s.getRange("Vehicle Sign Out!F28:G28"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F14:G14").copyTo(s.getRange("Vehicle Sign Out!F42:G42"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F14:G14").copyTo(s.getRange("Vehicle Sign Out!F56:G56"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D14").copyTo(s.getRange("Vehicle Sign Out!D28"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D14").copyTo(s.getRange("Vehicle Sign Out!D42"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D14").copyTo(s.getRange("Vehicle Sign Out!D56"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3110" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F15:G15").copyTo(s.getRange("Vehicle Sign Out!F29:G29"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F15:G15").copyTo(s.getRange("Vehicle Sign Out!F43:G43"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F15:G15").copyTo(s.getRange("Vehicle Sign Out!F57:G57"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D15").copyTo(s.getRange("Vehicle Sign Out!D29"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D15").copyTo(s.getRange("Vehicle Sign Out!D43"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D15").copyTo(s.getRange("Vehicle Sign Out!D57"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3111" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F16:G16").copyTo(s.getRange("Vehicle Sign Out!F30:G30"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F16:G16").copyTo(s.getRange("Vehicle Sign Out!F44:G44"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F16:G16").copyTo(s.getRange("Vehicle Sign Out!F58:G58"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D16").copyTo(s.getRange("Vehicle Sign Out!D30"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D16").copyTo(s.getRange("Vehicle Sign Out!D44"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D16").copyTo(s.getRange("Vehicle Sign Out!D58"), {contentsOnly: true});
  }

  else if (vehicle == "#BJS3112" && server == "Server 1") {

    s.getRange("Vehicle Sign Out!F17:G17").copyTo(s.getRange("Vehicle Sign Out!F31:G31"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F17:G17").copyTo(s.getRange("Vehicle Sign Out!F45:G45"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!F17:G17").copyTo(s.getRange("Vehicle Sign Out!F59:G59"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D17").copyTo(s.getRange("Vehicle Sign Out!D31"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D17").copyTo(s.getRange("Vehicle Sign Out!D45"), {contentsOnly: true});
    s.getRange("Vehicle Sign Out!D17").copyTo(s.getRange("Vehicle Sign Out!D59"), {contentsOnly: true});
  }
\$\endgroup\$

migrated from stackoverflow.com Apr 15 '18 at 5:27

This question came from our site for professional and enthusiast programmers.

1
\$\begingroup\$

Put all of your range notations into an object literal with inner arrays. See the object literal named objectRangeSettings Only check for server being "Server 1" once, at the top of the code, and if it's not Server 1 then quit.

You will need to enter all the range notations into the object. I only entered a few of them as an example, and the second one, #BJS3102, isn't correct.

Unless there is a way to calculate the ranges, then the ranges for each vehicle needs to be "hard coded." If there is some way to calculate what range is associated with which vehicle, then you could have a formula dynamically create the range notation. You probably can't change your fleet numbers, which have duplicates for each server. The column letters are the same for each server, so if the column letters in the range notation can be the same, no matter what the server, you could put that into a formula. Then the only "unknown" would be the row numbers for each server.

The row numbers for server 1 are 6 through 17. The row numbers for server 2 are 20 through 31, etc. So, the key is to associate the server number with the correct rows. The column letters will always be the same.

You could associate the last digit in #BJS310X with the row number for that server.

Run the code, and then in the View menu choose Logs, so see what is printed to the logs.

function copyPaste() {
  var i,key,L,lastDigitOfFleetNumber,objOfRowToServerAndFleetNmbr,objectRangeSettings,objOfTargetRowToServerAndFleetNmbr,
      rowNumberToUse,s,server,sourceRange,sourceRangeNotation,targetRange,thisRow,vehicle;

  s = SpreadsheetApp.getActiveSheet();
  vehicle = s.getRange("G2").getValue();
  server = s.getRange("G1").getValue();
  lastDigitOfFleetNumber = vehicle.slice(-1);//Get last digit of fleet number
  Logger.log('lastDigitOfFleetNumber: ' + lastDigitOfFleetNumber)

  objOfRowToServerAndFleetNmbr = {
    11:6,//Server 1 - Vehicle 1 - Row 6
    12:7,//Server 1 - Vehicle 2 - Row 7
    13:8,

    21:20,//Server 2 - Vehicle 1 - Row 20
    22:21,//Server 2 - Vehicle 2 - Row 21
    23:22,
    24:23,
  }

  objOfTargetRowToServerAndFleetNmbr = {
    1:[20,34,48],//Server 1 - Rows 20,34,48    
    2:[21,35,49],//Server 2 - 
    3:[22,36,50],//Server 3 -
  }

  key = lastDigitOfFleetNumber.toString + server.toString();
  Logger.log('key: ' + key)

  key = Number(key);//
  Logger.log('key: ' + key)

  rowNumberToUse = objOfRowToServerAndFleetNmbr[key];

  sourceRangeNotation = "Vehicle Sign Out!F" + rowNumberToUse + ":G" + rowNumberToUse;
  Logger.log('sourceRangeNotation: ' + sourceRangeNotation)

  L = 3;//Copy 3 rows

  for (i=0;i<L;i++) {//Loop 6 times to copy the rows to the targets 6 times
    thisRow = 
    targetRange = "Vehicle Sign Out!F" + thisRow + "G" + thisRow;
    Logger.log('sourceRange: ' + sourceRange)

    s.getRange(sourceRangeNotation).copyTo(s.getRange(targetRange), {contentsOnly: true});
  }

  L = 3;//Copy 3 more rows

  for (i=0;i<L;i++) {//Loop 6 times to copy the rows to the targets 6 times
    thisRow = 
    targetRange = "Vehicle Sign Out!F" + thisRow + "G" + thisRow;
    Logger.log('sourceRange: ' + sourceRange)

    s.getRange(sourceRangeNotation).copyTo(s.getRange(targetRange), {contentsOnly: true});
  }
}
\$\endgroup\$
  • \$\begingroup\$ I just added the loop. \$\endgroup\$ – Alan Wells Mar 10 '18 at 16:18
  • 1
    \$\begingroup\$ It could even be a double nested object - OP has 2 source ranges per vehicle, and each source range is used 3 times \$\endgroup\$ – tehhowch Mar 10 '18 at 16:40
  • \$\begingroup\$ I may be reading this wrong, but wouldn't I need to do one of these object arrays for each of the remaining vehicles (as you said), but then also replicate this whole thing for the other three servers? \$\endgroup\$ – Bryce Hanna Mar 11 '18 at 2:55
  • \$\begingroup\$ Unless there is a way to calculate the ranges, then the ranges for each vehicle needs to be "hard coded." If there is some way to calculate what range is associated with which vehicle, then you could have a formula dynamically create the range notation. You probably can't change your fleet numbers, which have duplicates for each server. The column letters are the same for each server, so if the column letters in the range notation can be the same, no matter what the server, you could put that into a formula. Then the only "unknown" would be the row numbers for each server. \$\endgroup\$ – Alan Wells Mar 11 '18 at 13:37
  • \$\begingroup\$ I made a major change to the code. The new code looks up the row numbers for the source and target range according to the vehicle number and server number, OR just by server number. I'm sure that the code isn't working correctly, but you should be able to see the logic. This strategy avoids needing to "hard code" the range settings for every vehicle and server, although you still need to hard code many settings. The only variables that change are the row numbers depending on the vehicle and the server number. \$\endgroup\$ – Alan Wells Mar 11 '18 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy