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.

I think you need to get the values first: source_sheet.getRange("A1:B2").getValues(); — Jacob Jan Tuinstra
Since you are new to Apps SCript, I suggest you run through the tutorials. See this one for working with spreadsheets developers.google.com/apps-script/guides/sheets — Srik
I went through the tutorials, and they are helpful indeed. thanks for the heads up! I'd found them before, but never took the time to read throught them. It definitely helped! — shaneshaneshane
I accepted your edit although it contains an error, see my edit below yours in the answer. — Serge insas
Is the Spreadsheet function =ImportRange() not appropriate for this purpose? — fooby

Serge insas Serge insas · Accepted Answer · 2013-06-17T09:51:34

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 ?

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

3 Answers