Copying google spreadsheet columns from one spreadsheet to another with a script

Question

I'd like to be able to copy a colum (well 3) from a spreadsheet to another spreadsheet (google docs spreadsheets). I'd also like there to be some sort of trigger that looks for modifications and automatically copies them to the new spreadsheet.

here is the code I have, that is not working! Thanks!

function copytest()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById("0AvxaM_p22RvwdHl2MFl4MkhteVhmVzFDY2FOblNpVEE");
  var source_sheet = ss.getSheetByName("Sheet1");
  var target_sheet = target.getSheetByName("Sheet1");
  var source_range = source_sheet.getRange("A1:B2");

  source_range.copyTo("A1:B2");

}

I think its all working except the last line source_range.copyTo("A1:B2"); I'm new to this scripting business, so thanks for your help.

Answer 1

the copyTo() method works only when destination is in the same spreadsheet. To copy data from one spreadsheet to another you will have to get the values (and whatever format, colors, fontsize...) from the source range into a variable (a 2D array or as indicated in the tutorial that Srik was referring to) and write it back to the target range.

for example :

function copytest()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById("0AnqSFd3iikE3dEpHUGJod2xwbXRqU25wS25HWF9pdEE");
  var source_sheet = ss.getSheetByName("Sheet1");
  var target_sheet = target.getSheetByName("Sheet1");
  var source_range = source_sheet.getRange("A1:B2");
  var target_range = target_sheet.getRange("A1:B2");

  var values = source_range.getValues();
  var bGcolors = source_range.getBackgrounds();
  var colors = source_range.getFontColors();
  var fontSizes = source_range.getFontSizes();
  // make use of autocomplete to find other methods...
  target_range.setValues(values);
  target_range.setBackgrounds(bGcolors);
  target_range.setFontColors(colors);
  target_range.setFontSizes(fontSizes);
}

NEW UPDATED CODE IS AS FOLLOWS (EDIT BY QUESTION AUTHOR)

function copyTest()
{

//gets and formats data ranges, to and from sheets.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var target = SpreadsheetApp.openById("idspispopd1234567890");
var source_sheet = ss.getSheetByName("one");
var target_sheet = target.getSheetByName("Sheet1");
var source_range = source_sheet.getRange("A2:A");
var target_range = target_sheet.getRange("A2:A");

//gets then writes values defined in setup
var values = source_range.getValues();
target_range.setValues(values);

//for checking last row with data in cell and formatting range
var lastRow = source_sheet.getLastRow();  

//FOR MAIL
var emailAddress = "[email protected]";
var subject = "Compass import information from Montessori Community School";
var message = values;
MailApp.sendEmail(emailAddress, subject, message);

}

---

EDIT 2 (BY ANSWERER)

to get a full column, change the range definition like this :

var source_range = source_sheet.getRange("A2:A");
var values = source_range.getValues();
var target_range = target_sheet.getRange(2,1,values.length,values[0].length);// use the array size to define the range because the target column A is not necessarily the same size at this time.

if you want to copy multiple contiguous columns just define the source sheet accordingly, in A1 notation column A+B+C would be .getRange("A2:C");.

Try to use the integer definition for range, IMO it's more easy and more readable : for example a range of full columns A+B+C would be sh.getRange(1,1,sh.getLastRow(),3)

For non contiguous columns it's becoming a bit more complicated.... maybe a new thread ?

Answer 2

I have found that it is easier to just copy the source_sheet to the target_spreadsheet, copy the necessary range using copyTo then delete the copied source_sheet.

 // 1. Open source sheet
 let spreadsheetFrom = SpreadsheetApp.openById(source_spreadsheet_id);
 let sheetFrom = spreadsheetFrom.getSheetByName(source_sheet_name);

 // 2. Copy source sheet to target spreadsheet
 let spreadsheetTo = SpreadsheetApp.openById(target_spreadsheet_id);
 let sheetCopy = sheetFrom.copyTo(spreadsheetTo);

 // 3. Copy range from source_sheet's copy to target_sheet
 let rangeFrom = sheetCopy.getRange('XX:YY');    
 let sheetTo = spreadsheetTo.getSheetByName(target_sheet_name);
 let rangeTo = sheet.getRange('XX:YY');
 rangeFrom.copyTo(rangeTo);

 // 4. Delete source_sheet's copy
 spreadsheetTo.deleteSheet(sheetCopy);