How do you copy/paste from one Google Sheet Tab, to another google sheet and specify the tab in the target google sheet?

Question

I'm creating a Google App Script, for a Google Sheet Document. I need to allow a user to select a range of cells in the Source Google Sheet and then be able to pic the specific tab in the target google sheet, to paste that data to.

I've been able to hardcode a TARGET tab, which in another google sheet, but I have not been able to figure out how to make it so that the user can pick the specific tab that way to copy the data to.

This is my first endeavor into coding. I am a 100% novice.

function GeneralToTracking() {

  /*
  This code defines the Source Google Sheet Doc and the Target Google Sheet Doc. These are two
  different google sheet docs. They are NOT 2 sheets in the same google sheet doc.
  */

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById("1yxfpC7Yag9GAkoe5BUjjg12cUhGmGr5ryeGl87JmZqU");

  /*
  This code is to pick specific sheets within the Source & Target Sheet.
     Source Google Sheet = "New Stuff"
     Target Google Sheet = "Archive"
  */

  var source_sheet = ss.getActiveSheet();
  var target_sheet = target.getSheetByName("Archive"); // ++++ TO DO: Need to present the user with a list of tabs in the Target doc. Prompt w/ Radio Buttons. ++++

  /* 
  This code determines the from-range and the to-range to copy & says where to put it in the Target.
  */

  var source_range = source_sheet.getActiveRange();
  var last_row = target_sheet.getLastRow();

//  source_range.copyTo(target_range);

  if (last_row > 0) target_sheet.insertRowAfter(last_row);
  var target_range = target_sheet.getRange(last_row + 1, 1);
  var copiedsheet = source_sheet.copyTo(target);
  copiedsheet.getRange(source_range.getA1Notation()).copyTo(target_range);
  target.deleteSheet(copiedsheet);

}

Can I ask you about your situation? 1. Is the active Spreadsheet different from the Spreadsheet of 1yxfpC7Yag9GAkoe5BUjjg12cUhGmGr5ryeGl87JmZqU? 2. Can you provide the information about this code didn't work? If the error occurs, please show it. 3. Are 2 comments ++++ TO DO: ... ++++ in your script related to your question? — Tanaike
Hey, Tanaike. Thank you very much for your questions. Regarding your 2nd question: I changed my code back, so I could get the error message and provide it to you. HOWEVER, when I changed the code, it actually started working!!! No idea what I did previously, but it works like a charm now. Regarding your question about the “TO DO” comments. The first “to do” was the question I’m asking here. The second “to do” is the next part I need to look into and figure out how to do. ra89fi actually tried helping me with that below. — Adam Christiansen

ra89fi ra89fi · Accepted Answer · 2019-04-16T06:18:18

Below code adds a custom menu to sheet, with an item which upon click shows a prompt to get the target sheet name. Enter that and it copies current sheet active range to target sheet last row. Save and reload sheet.

// ------------ GENERAL GOOGLE SHEET DOC TO TRACKING GOOGLE SHEET DOC ------------

function GeneralToTracking(tName) {
  /*
  This code defines the Source Google Sheet Doc and the Target Google Sheet Doc. These are two
  different google sheet docs. They are NOT 2 sheets in the same google sheet doc.
  */

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById('1yxfpC7Yag9GAkoe5BUjjg12cUhGmGr5ryeGl87JmZqU');

  /*
  This code is to pick specific sheets within the Source & Target Sheet.
     Source Google Sheet = "New Stuff"
     Target Google Sheet = "Archive"
  */

  var source_sheet = ss.getActiveSheet(); // ++++ TO DO: Need to make this work on whatever sheet the user is on currently, instead of being hardcoded. ++++
  var target_sheet = target.getSheetByName(tName); // ++++ TO DO: Need to present the user with a list of tabs in the Target doc. Prompt w/ Radio Buttons. ++++

  /* 
  This code determines the from-range and the to-range to copy & says where to put it in the Target.
  */

  var source_range = source_sheet.getActiveRange();
  var sValues = source_range.getValues();
  var last_row = target_sheet.getLastRow();

  //  source_range.copyTo(target_range);

  if (last_row > 0) target_sheet.insertRowAfter(last_row);
  var target_range = target_sheet.getRange(last_row + 1, 1, sValues.length, sValues[0].length);
  target_range.setValues(sValues);

  // double check and enable these when above test is pass
  // var copiedsheet = source_sheet.copyTo(target);
  // copiedsheet.getRange(source_range.getA1Notation()).copyTo(target_range);
  // target.deleteSheet(copiedsheet);
}

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
    .createMenu('Custom Menu')
    .addItem('Enter Target Name', 'enterTargetName')
    .addToUi();
}

function enterTargetName() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
    'Enter Target Sheet Name',
    'Please enter target sheet name:',
    ui.ButtonSet.OK_CANCEL
  );

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    GeneralToTracking(text);
  }
}

How do you copy/paste from one Google Sheet Tab, to another google sheet and specify the tab in the target google sheet?

1 Answers