CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Petrus
Petrus

Reputation: 321

Copying / transferring from one spreadsheet to another code based

I'm trying to copy / transfer a sheet from one spreadsheet to another spreadsheet. I have tried various methods and .copyTo seems to be the best and most efficient way.

.copyTo works well but I'm struggling to send to a specific sheet...

Here is my code: 

 function TransferDataOut() {

 var source = SpreadsheetApp.getActiveSpreadsheet();
 var sheetA = source.getSheets()[0]; //sheet source number  

 var destination = SpreadsheetApp.openById('the destination sheet');
 var each = "Data_Incoming"; 

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 sheetA.copyTo(destination); // I tried renaming .setName(each);


 }

So if I only use sheetA.copyTo(destination); it simply creates a copy sheet, like Copy of the_souce_sheet_name. If I try renaming to make it a specific name I will get error after running for the second time that the sheet already exists in destination spreadsheet.

What I really need to achieve is that the function from source spreadsheet copies data from the source sheet to always the same sheet in destination spreadsheet. Perhaps .copyTo is not the right way to do it? Any suggestions and code will help please!

The reason for receiving data on a exact sheet in the destination spreadsheet is because I have a trigger On change that executes another script to work with the new incoming data.

Upvotes: 1

Views: 264

Answers (1)

Tanaike
Tanaike

Reputation: 201703

  • You want to overwrite the source sheet of Spreadsheet A to the destination sheet of Spreadsheet B.
  • You want to keep the sheet name of var each = "Data_Incoming".

If my understanding is correct, how about this answer? I would like to propose 2 samples. So please select one of them for your situation.

Sample script 1:

The flow of this sample script is as follows.

  1. each sheet of Spreadsheet B is deleted.
  2. Source sheet of Spreadsheet A is copied to the destination sheet of Spreadsheet B.
  3. Sheet name of the copied sheet of Spreadsheet B is modified to each.

Modified script:

function TransferDataOut() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA = source.getSheets()[0]; //sheet source number
  var destination = SpreadsheetApp.openById('the destination sheet');
  var each = "Data_Incoming"; 
  var destSheet = destination.getSheetByName(each);
  if (destSheet) {
    destination.deleteSheet(destSheet);
  }
  sheetA.copyTo(destination).setName(each);
}

Sample script 2:

The flow of this sample script is as follows.

  1. Copy the source sheet of Spreadsheet A to Spreadsheet B.

  2. If the sheet with the sheet name of each is not existing in Spreadsheet B, the copied sheet is renamed to each.

  3. If the sheet with the sheet name of each is existing in Spreadsheet B, the source sheet is copied to the Spreadsheet B as Copy of ###.

  4. each sheet is cleared.
  5. All values, formulas and formats of the copied sheet are copied to each sheet.
  6. Delete the copied sheet.

Modified script:

function TransferDataOut() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA = source.getSheets()[0]; //sheet source number  
  var destination = SpreadsheetApp.openById('the destination sheet');
  var each = "Data_Incoming";
  var copiedSheet = sheetA.copyTo(destination);
  var destSheet = destination.getSheetByName(each);
  if (destSheet) {
    destSheet.clear();
    var srcRange = copiedSheet.getDataRange();
    srcRange.copyTo(destSheet.getRange(srcRange.getA1Notation()));
    destination.deleteSheet(copiedSheet);
  } else {
    copiedSheet.setName(each);
  }
}

Note:

  • In this sample script, each sheet of the Spreadsheet B (destination Spreadsheet) is deleted. So please be careful this.
    • So at first, as a test, I recommend to use a sample Spreadsheet.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Added:

  • You want to run the script of destination Spreadsheet when the source values are copied to the destination Spreadsheet using the script in the source Spreadsheet.

The following sample script is for achieving above situation.

Script of destination Spreadsheet:

At first, the script of destination Spreadsheet is prepared.

Script: 
function doGet() {

  sample(); // This is the function that you want to run when the source values are copied.

  return ContentService.createTextOutput();
}

After copy and paste above script to the script editor of destination Spreadsheet, please do the following flow.

  • Deploy Web Apps.
    1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
    2. Select "Me" for "Execute the app as:".
    3. Select "Anyone, even anonymous" for "Who has access to the app:".
    4. Click "Deploy" button as new "Project version".
    5. Automatically open a dialog box of "Authorization required".
      1. Click "Review Permissions".
      2. Select own account.
      3. Click "Advanced" at "This app isn't verified".
      4. Click "Go to ### project name ###(unsafe)"
      5. Click "Allow" button.
    6. Copy "Current web app URL:".
      • It's like https://script.google.com/macros/s/#####/exec.
    7. Click "OK".

Script of source Spreadsheet:

As the next step, the script of source Spreadsheet is prepared as follows. In this sample, the sample 2 was used. I think that you can also use the sample 1.

Script: 
function TransferDataOut() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA = source.getSheets()[0]; //sheet source number  
  var destination = SpreadsheetApp.openById('the destination sheet');
  var each = "Data_Incoming";
  var copiedSheet = sheetA.copyTo(destination);
  var destSheet = destination.getSheetByName(each);
  if (destSheet) {
    destSheet.clear();
    var srcRange = copiedSheet.getDataRange();
    srcRange.copyTo(destSheet.getRange(srcRange.getA1Notation()));
    destination.deleteSheet(copiedSheet);
  } else {
    copiedSheet.setName(each);
  }

  // Added
  var url = "https://script.google.com/macros/s/###/exec"; // Please set the retrieved URL of Web Apps.
  UrlFetchApp.fetch(url);
}

Note:

  • By above settings, when TransferDataOut() of the source Spreadsheet was run, doGet() of the destination Spreadsheet is run by UrlFetchApp.fetch(url).

References:

Upvotes: 2

Related Questions