Wednesday, June 26, 2013

Google Spreadsheet How to copy values to a different spreadsheet

Question:

how to copy values to a different spreadsheet using apps script

Browser: Google Chrome
OS: Mac Os 10.8.4

Hi! I am trying to create a script that copies a sheet to different spreadsheet file. I manually do this by selecting the range, then paste special, then values only, finally I click on the sheet's tab and select 'copy to' and choose the destination. I have been trying to do the same operation through apps script but can't get the values, I only get the format and formulas.

function copiaConsolidado()
{
  var hoja = SpreadsheetApp.getActiveSheet();
  var destino = SpreadsheetApp.openById('id');
  hoja.copyTo(destino);
}


Solution:

Try the following script:

function copySheetValues()
{
  var source = SpreadsheetApp.getActiveSheet();
  var sourcename = source.getSheetName();
  var sourceDataRange = source.getDataRange();
  var sourceSheetValues = sourceDataRange.getValues();
  var sourceRows = sourceDataRange.getNumRows();
  var sourceColumns = sourceDataRange.getNumColumns();
  
  var destination = SpreadsheetApp.openById('0AmMTqpzD9YRndFh5aWk0UUhmejJMMXZWYUZkSDRsY0E');
  destination.insertSheet(sourcename, 0);
  destination.getDataRange().offset(0, 0, sourceRows, sourceColumns).setValues(sourceSheetValues);

}


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 



2 comments:

  1. Thanks a lot, Kishan!
    This is really helpful and it does exactly what I am looking for. I really appreciate your help. Just one more question: is there a way to copy the formatting as well?
    I got all the values copied to the target spreadsheet but I would like to have the same result that I get when I do all the operation 'manually'.

    ReplyDelete
  2. Kishan, I think your option will work better for me, as my solution is very slow and clunky, yet I'm having difficulty getting yours to work. Could you assist? Here's what I'm currently using:

    function transferAttendance() {
    var inputsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Daily Attendance");//Locates Input Sheet
    var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Transfer"); // Locates Source Sheet
    var sourceData = sourceSheet.getDataRange().getValues(); // Locates Source Data
    var targetSS = SpreadsheetApp.openById("1NlBxs0WXp8wXNkXH3d9mJ1Ybr5dU7Qj4QMvAKxdCtug").getSheetByName("AttendanceRaw"); // Locates Transfer Sheet

    for (var r in sourceData) {
    var row = sourceData[r];
    if (row[0] !== "") {

    if (row[2] !== ""|| row[3] !== "") { // Added by PB - Only writes to targetSS document IF there is status or engagment data
    targetSS.appendRow(row);
    }


    }
    }
    inputsheet.getRange('B2:B').clearContent();
    inputsheet.getRange('C2:C').clearContent();

    }

    ReplyDelete