CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscopy-paste
Adam Christiansen
Adam Christiansen

Reputation: 15

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

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);

}

Upvotes: 0

Views: 1077

Answers (1)

ra89fi
ra89fi

Reputation: 1245

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);
  }
}

Upvotes: 0

Related Questions