Skip to main content
Code Review

Return to Question

Notice removed Canonical answer required by Community Bot
Bounty Ended with Blindman67's answer chosen by Community Bot
Corrected function6 call to function4, and function5 call to function3
Source Link 
//Functions that copy and paste values
function copyPasteValues(){
 var function1 = function(sheet, copyRange, pasteRange){
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet)
 if(Array.isArray(copyRange) && Array.isArray(pasteRange)){
 ss.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 //Should there be a typeof check for string here? (Throwing more errors)
 } else{
 ss.getRange(copyRange).copyTo(ss.getRange(pasteRange))
 } 
 }
 var function2 = function(sourceSheet, targetSheet, copyRange, pasteRange){
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
 if(Array.isArray(copyRange) && Array.isArray(pasteRange)){
 sa.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 } else{
 ss.getRange(copyRange).copyTo(sa.getRange(pasteRange))
 }
 }
 var function3 = function(sourceSheet, targetSheet, spreadsheetID, copyRange, pasteRange){
 //Pushing data to another spreadsheet and pasting it to another
 try{
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.openById(spreadsheetID).getSheetByName(targetSheet)
 ss.getRange(copyRange).setValues(sa.getRange(pasteRange).getValues())
 //Pulling data from another spreadsheet and pasting it
 } catch(e){
 if(e instanceof Error){
 let ss = SpreadsheetApp.openById(spreadsheetID).getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
 ss.getRange(copyRange).setValues(sa.getRange(pasteRange).getValues())
 } else{
 throw e
 }
 }
 }
 var function4 = function(sourceSheet, targetSheet, spreadsheetID, copyRange, pasteRange){
 //Pushing data from another spreadsheet and pasting it to another
 try{
 if(arrayCheck(copyRange, pasteRange)){
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.openById(spreadsheetID).getSheetByName(targetSheet)
 sa.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 }
 //Pulling data from another spreadsheet and pasting it to another
 } catch(e){
 if(e instanceof Error){
 let ss = SpreadsheetApp.openById(spreadsheetID).getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
 sa.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 } else{
 throw e
 }
 }
 }
 try{
 if(arguments.length === 3){
 function1(arguments[0], arguments[1], arguments[2])
 } else if(arguments.length === 4){
 function2(arguments[0], arguments[1], arguments[2], arguments[3])
 } else if(arguments.length === 5){
 if(Array.isArray(arguments[3]) && Array.isArray(arguments[4])){
 function6function4(arguments[0], arguments[1], arguments[2], arguments[3], arguments[4])
 } else{
 function5function3(arguments[0], arguments[1], arguments[2], arguments[3], arguments[4])
 }
 }
 } catch(e){
 Logger.log(e)
 }
}

//Functions that copy and paste values
function copyPasteValues(){
 var function1 = function(sheet, copyRange, pasteRange){
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet)
 if(Array.isArray(copyRange) && Array.isArray(pasteRange)){
 ss.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 //Should there be a typeof check for string here? (Throwing more errors)
 } else{
 ss.getRange(copyRange).copyTo(ss.getRange(pasteRange))
 } 
 }
 var function2 = function(sourceSheet, targetSheet, copyRange, pasteRange){
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
 if(Array.isArray(copyRange) && Array.isArray(pasteRange)){
 sa.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 } else{
 ss.getRange(copyRange).copyTo(sa.getRange(pasteRange))
 }
 }
 var function3 = function(sourceSheet, targetSheet, spreadsheetID, copyRange, pasteRange){
 //Pushing data to another spreadsheet and pasting it to another
 try{
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.openById(spreadsheetID).getSheetByName(targetSheet)
 ss.getRange(copyRange).setValues(sa.getRange(pasteRange).getValues())
 //Pulling data from another spreadsheet and pasting it
 } catch(e){
 if(e instanceof Error){
 let ss = SpreadsheetApp.openById(spreadsheetID).getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
 ss.getRange(copyRange).setValues(sa.getRange(pasteRange).getValues())
 } else{
 throw e
 }
 }
 }
 var function4 = function(sourceSheet, targetSheet, spreadsheetID, copyRange, pasteRange){
 //Pushing data from another spreadsheet and pasting it to another
 try{
 if(arrayCheck(copyRange, pasteRange)){
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.openById(spreadsheetID).getSheetByName(targetSheet)
 sa.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 }
 //Pulling data from another spreadsheet and pasting it to another
 } catch(e){
 if(e instanceof Error){
 let ss = SpreadsheetApp.openById(spreadsheetID).getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
 sa.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 } else{
 throw e
 }
 }
 }
 try{
 if(arguments.length === 3){
 function1(arguments[0], arguments[1], arguments[2])
 } else if(arguments.length === 4){
 function2(arguments[0], arguments[1], arguments[2], arguments[3])
 } else if(arguments.length === 5){
 if(Array.isArray(arguments[3]) && Array.isArray(arguments[4])){
 function6(arguments[0], arguments[1], arguments[2], arguments[3], arguments[4])
 } else{
 function5(arguments[0], arguments[1], arguments[2], arguments[3], arguments[4])
 }
 }
 } catch(e){
 Logger.log(e)
 }
}

//Functions that copy and paste values
function copyPasteValues(){
 var function1 = function(sheet, copyRange, pasteRange){
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet)
 if(Array.isArray(copyRange) && Array.isArray(pasteRange)){
 ss.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 //Should there be a typeof check for string here? (Throwing more errors)
 } else{
 ss.getRange(copyRange).copyTo(ss.getRange(pasteRange))
 } 
 }
 var function2 = function(sourceSheet, targetSheet, copyRange, pasteRange){
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
 if(Array.isArray(copyRange) && Array.isArray(pasteRange)){
 sa.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 } else{
 ss.getRange(copyRange).copyTo(sa.getRange(pasteRange))
 }
 }
 var function3 = function(sourceSheet, targetSheet, spreadsheetID, copyRange, pasteRange){
 //Pushing data to another spreadsheet and pasting it to another
 try{
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.openById(spreadsheetID).getSheetByName(targetSheet)
 ss.getRange(copyRange).setValues(sa.getRange(pasteRange).getValues())
 //Pulling data from another spreadsheet and pasting it
 } catch(e){
 if(e instanceof Error){
 let ss = SpreadsheetApp.openById(spreadsheetID).getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
 ss.getRange(copyRange).setValues(sa.getRange(pasteRange).getValues())
 } else{
 throw e
 }
 }
 }
 var function4 = function(sourceSheet, targetSheet, spreadsheetID, copyRange, pasteRange){
 //Pushing data from another spreadsheet and pasting it to another
 try{
 if(arrayCheck(copyRange, pasteRange)){
 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.openById(spreadsheetID).getSheetByName(targetSheet)
 sa.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 }
 //Pulling data from another spreadsheet and pasting it to another
 } catch(e){
 if(e instanceof Error){
 let ss = SpreadsheetApp.openById(spreadsheetID).getSheetByName(sourceSheet)
 let sa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
 sa.getRange(pasteRange[0], pasteRange[1], copyRange[2], copyRange[3]).setValues(ss.getRange(copyRange[0], copyRange[1], copyRange[2], copyRange[3]).getValues())
 } else{
 throw e
 }
 }
 }
 try{
 if(arguments.length === 3){
 function1(arguments[0], arguments[1], arguments[2])
 } else if(arguments.length === 4){
 function2(arguments[0], arguments[1], arguments[2], arguments[3])
 } else if(arguments.length === 5){
 if(Array.isArray(arguments[3]) && Array.isArray(arguments[4])){
 function4(arguments[0], arguments[1], arguments[2], arguments[3], arguments[4])
 } else{
 function3(arguments[0], arguments[1], arguments[2], arguments[3], arguments[4])
 }
 }
 } catch(e){
 Logger.log(e)
 }
}
Updated the 'updated function' to the newest version
Source Link

Updated Version (2/37/2023)

Hello, hereHere is an even more updated version of whatone that I feel is more legible and understandable, as the previous updated one was just brute forcing it looks likebased on argument length. It's very crude, but it's closerMost of the checks are going to whatbe used for error handling once I want it to look like/function likefeel comfortable with where the program currently sits

/*function copyValues(){
args[0] = copyRangetry{
args[1] const options = pasteRange{
args[2] = sheet/sourceSheet copy: arguments[0],
args[3] = targetSheet paste: arguments[1],
args[4] = ss_ID source: arguments[2]
*/ }
function copyValues if(arguments.length >= 4){
 let args options.target = argumentsarguments[3]
 try{
 if(argsarguments.length ===>= 35){
 let ss options.ss_ID = spreadsheet.getSheetByName(args[2])arguments[4]
 }
 }
 if(ArrayarrayCheck(options.isArraycopy, options.paste)){
 switch(args[0]true){
 && Array case arguments.isArraylength === 3:
 ss = spreadsheet.getSheetByName(args[1])options.source){
 ss.getRange(args[1][0]options.paste[0], args[1][1]options.paste[1], args[0][2]options.copy[2], args[0][3]options.copy[3]).setValues(ss.getRange(args[0][0]options.copy[0], args[0][1]options.copy[1], args[0][2]options.copy[2], args[0][3]options.copy[3]).getValues())
 } else if(String(args[0]) === args[0]break;
 && String(args[1]) === args[0]){
 sscase arguments.getRangelength === 4:
 arrayPaste(args[1])options.setValues(sscopy, options.getRangepaste, spreadsheet.getSheetByName(args[0]options.source), spreadsheet.getValuesgetSheetByName(options.target))
 }
 }break;

 if(args case arguments.length === 4){5:
 let ssobj = spreadsheet.getSheetByNamespreadsheetCheck(args[2]options.source, options.target, options.ss_ID)
 let sa = spreadsheet.getSheetByName arrayPaste(args[3]options.copy, options.paste, obj.ss, obj.sa)
 break;
 }
 } else if(Array.isArraystringCheck(args[0]options.copy, options.paste)){
 && Array.isArray switch(args[1])true){
 case arguments.length === 3:
  ss = spreadsheet.getRangegetSheetByName(args[1][0],options.source)
 args[1][1], args[0][2], args[0][3] ss.getRange(options.paste).setValues(sass.getRange(args[0][0], args[0][1], args[0][2], args[0][3]options.copy).getValues())
 } else if(String(args[0]) === args[0]break;
 && String(args[1]) case arguments.length === args[1]){4:
 sa.getRange stringPaste(args[1])options.setValues(sscopy, options.getRangepaste, spreadsheet.getSheetByName(args[0]options.source), spreadsheet.getValuesgetSheetByName(options.target))
 }
 }break;

 if(args case arguments.length === 5){:
 let obj = spreadsheetCheck(options.source, options.target, options.ss_ID)
 stringPaste(options.copy, options.paste, obj.ss, obj.sa)
  }
 }
 } catch(e){
 Logger.log(e)
 }
 function spreadsheetCheck(source, target, ss_ID){
 let ss, sa
 switch(true){
 if( case SpreadsheetApp.openById(args[4]ss_ID).getSheetByName(args[2]source) === null){:
 ss = spreadsheet.getSheetByName(args[2]source);
 sa = SpreadsheetApp.openById(args[4]ss_ID).getSheetByName(args[3]target);
 } break;

 elsecase if(SpreadsheetApp.openById(args[4]ss_ID).getSheetByName(args[3]target) === null){:
 ss = SpreadsheetApp.openById(args[4]ss_ID).getSheetByName(args[2]source);
 sa = spreadsheetSpreadsheetApp.getActiveSpreadsheet().getSheetByName(args[3]target);
 } break;
 }
 if(Array.isArray(args[0]) && Array.isArray(args[1]))return {ss, sa}
 }
 function arrayPaste(copy, paste, ss, sa){
 sa.getRange(args[1][0]paste[0], args[1][1]paste[1], args[0][2]copy[2], args[0][3]copy[3]).setValues(ss.getRange(args[0][0]copy[0], args[0][1]copy[1], args[0][2]copy[2], args[0][3]copy[3]).getValues())
 } else if(String(args[0]) === args[0] &&function StringstringPaste(args[1])copy, ===paste, args[0]ss, sa){
 sa.getRange(args[1]paste).setValues(ss.getRange(args[0]copy).getValues())
 } 
 function arrayCheck(copy, paste){
 if(Array.isArray(copy) && Array.isArray(paste)){
 return true
 } else{
 return false
 }
 } catch function stringCheck(ecopy, paste){
 Logger.logif(eString(copy) === copy && String(paste) === paste){
 return true
 } else{
 return false
 }
 }
}

Updated Version (2/3/2023)

Hello, here is an updated version of what it looks like. It's very crude, but it's closer to what I want it to look like/function like

/*
args[0] = copyRange
args[1] = pasteRange
args[2] = sheet/sourceSheet
args[3] = targetSheet
args[4] = ss_ID
*/
function copyValues(){
 let args = arguments
 try{
 if(args.length === 3){
 let ss = spreadsheet.getSheetByName(args[2])
 if(Array.isArray(args[0]) && Array.isArray(args[1])){
 ss.getRange(args[1][0], args[1][1], args[0][2], args[0][3]).setValues(ss.getRange(args[0][0], args[0][1], args[0][2], args[0][3]).getValues())
 } else if(String(args[0]) === args[0] && String(args[1]) === args[0]){
 ss.getRange(args[1]).setValues(ss.getRange(args[0]).getValues())
 }
 }
 if(args.length === 4){
 let ss = spreadsheet.getSheetByName(args[2])
 let sa = spreadsheet.getSheetByName(args[3])
 if(Array.isArray(args[0]) && Array.isArray(args[1])){
 ss.getRange(args[1][0], args[1][1], args[0][2], args[0][3]).setValues(sa.getRange(args[0][0], args[0][1], args[0][2], args[0][3]).getValues)
 } else if(String(args[0]) === args[0] && String(args[1]) === args[1]){
 sa.getRange(args[1]).setValues(ss.getRange(args[0]).getValues())
 }
 }
 if(args.length === 5){
 let ss 
 let sa
 if(SpreadsheetApp.openById(args[4]).getSheetByName(args[2]) === null){
 ss = spreadsheet.getSheetByName(args[2]);
 sa = SpreadsheetApp.openById(args[4]).getSheetByName(args[3]);
 }
 else if(SpreadsheetApp.openById(args[4]).getSheetByName(args[3]) === null){
 ss = SpreadsheetApp.openById(args[4]).getSheetByName(args[2]);
 sa = spreadsheet.getSheetByName(args[3]);
 }
 if(Array.isArray(args[0]) && Array.isArray(args[1])){
 sa.getRange(args[1][0], args[1][1], args[0][2], args[0][3]).setValues(ss.getRange(args[0][0], args[0][1], args[0][2], args[0][3]).getValues())
 } else if(String(args[0]) === args[0] && String(args[1]) === args[0]){
 sa.getRange(args[1]).setValues(ss.getRange(args[0]).getValues())
 }
 }
 } catch(e){
 Logger.log(e)
 }
}

Updated Version (2/7/2023)

Here is an even more updated one that I feel is more legible and understandable, as the previous updated one was just brute forcing it based on argument length. Most of the checks are going to be used for error handling once I feel comfortable with where the program currently sits

function copyValues(){
 try{
 const options = {
  copy: arguments[0],
  paste: arguments[1],
  source: arguments[2]
 }
  if(arguments.length >= 4){
 options.target = arguments[3]
 if(arguments.length >= 5){
 options.ss_ID = arguments[4]
 }
 }
 if(arrayCheck(options.copy, options.paste)){
 switch(true){
  case arguments.length === 3:
 ss = spreadsheet.getSheetByName(options.source)
 ss.getRange(options.paste[0], options.paste[1], options.copy[2], options.copy[3]).setValues(ss.getRange(options.copy[0], options.copy[1], options.copy[2], options.copy[3]).getValues())
 break;
 
 case arguments.length === 4:
 arrayPaste(options.copy, options.paste, spreadsheet.getSheetByName(options.source), spreadsheet.getSheetByName(options.target))
 break;

  case arguments.length === 5:
 let obj = spreadsheetCheck(options.source, options.target, options.ss_ID)
  arrayPaste(options.copy, options.paste, obj.ss, obj.sa)
 break;
 }
 } else if(stringCheck(options.copy, options.paste)){
  switch(true){
 case arguments.length === 3:
  ss = spreadsheet.getSheetByName(options.source)
  ss.getRange(options.paste).setValues(ss.getRange(options.copy).getValues())
 break;
 case arguments.length === 4:
  stringPaste(options.copy, options.paste, spreadsheet.getSheetByName(options.source), spreadsheet.getSheetByName(options.target))
 break;

  case arguments.length === 5:
 let obj = spreadsheetCheck(options.source, options.target, options.ss_ID)
 stringPaste(options.copy, options.paste, obj.ss, obj.sa)
  }
 }
 } catch(e){
 Logger.log(e)
 }
 function spreadsheetCheck(source, target, ss_ID){
 let ss, sa
 switch(true){
  case SpreadsheetApp.openById(ss_ID).getSheetByName(source) === null:
 ss = spreadsheet.getSheetByName(source)
 sa = SpreadsheetApp.openById(ss_ID).getSheetByName(target)
  break;

 case SpreadsheetApp.openById(ss_ID).getSheetByName(target) === null:
 ss = SpreadsheetApp.openById(ss_ID).getSheetByName(source)
 sa = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(target)
  break;
 }
 return {ss, sa}
 }
 function arrayPaste(copy, paste, ss, sa){
 sa.getRange(paste[0], paste[1], copy[2], copy[3]).setValues(ss.getRange(copy[0], copy[1], copy[2], copy[3]).getValues())
 } function stringPaste(copy, paste, ss, sa){
 sa.getRange(paste).setValues(ss.getRange(copy).getValues())
 } 
 function arrayCheck(copy, paste){
 if(Array.isArray(copy) && Array.isArray(paste)){
 return true
 } else{
 return false
 }
 }  function stringCheck(copy, paste){
 if(String(copy) === copy && String(paste) === paste){
 return true
 } else{
 return false
 }
 }
}
Changed the name
Link

Copy and Pasting Values from One Spreadsheet to AnotherSpreadsheets

Notice added Canonical answer required by Lofton Gentry
Bounty Started worth 50 reputation by Lofton Gentry
Providing crude but updated version of the code, however not quite where I want it to be
Source Link
Loading
Source Link
Loading
lang-js

AltStyle によって変換されたページ (->オリジナル) /