CODEWITHSUNDEEP
google-apps-script
CorbinS
CorbinS

Reputation: 23

Copy a row with specific cells to another spreadsheet

I have been searching for a way to copy certain cells from a row and paste them into another spreadsheet, but all I can seem to find are ways to do that just from sheet to sheet within one spreadsheet or questions that deal with code way above my head. I'm just now learning to code in Google Spreadsheets and I can't seem to get it right. Here is my code for now, I'm working on just copying one cell first and after I can do that I'll get a loop to iterate through the row and pick the cells I want to copy. 

var ss = SpreadsheetApp.getActiveSpreadsheet();
var master = SpreadsheetApp.openById('0AgcCWQn-aoI1dFpLVE4tSENwcThrYnlMUzhuRmdWU2c');
var target = SpreadsheetApp.openById('0AgcCWQn-aoI1dF96X2dBT2dVVFZ2SU1NRWdYTDJhT2c');

var master_sheet = master.getSheetByName("Steve2");
var target_sheet = target.getSheetByName("Corbin1");

var master_range = master_sheet.getRange("A1");
var target_range = target_sheet.getRange("A1");

master_range.copyTo(target_range);

Right now it is giving me an error saying that it cannot call the getRange method of null. I'm not sure if I'm using OpenById correctly or if I can even use that in this situation.

OK I've got it working now. It copies the row perfectly and it copies only the cells I want. Now I need help with the pasting part. It copies everything fine and puts it into the other sheet great, but I need it to copy into the next available row and into the first available columns. So if row 5 is the last column of data, I need it to paste into row 6 and take up the first 6 or so columns.
This is what I have so far.

function menuItem1() {
var sss = SpreadsheetApp.openById('0AgcCWQn-aoI1dFpLVE4tSENwcThrYnlMUzhuRmdWU2c'); // sss = source spreadsheet Steve2
var ss = sss.getSheetByName('Sheet1'); // ss = source sheet

//Message box asking user to specify the row to be copied
var answer = Browser.inputBox('What row would you like to copy?');
var range = parseInt(answer);             

var array = [1,2,3,9,12,30];             

//Runs a loop to iterate through array, using array elements as column numbers
for(var i = 0; i < array.length; i++){
var SRange = ss.getRange(answer,1,1,array[i]);
//get A1 notation identifying the range
var A1Range = SRange.getA1Notation();
//get the data values in range
var SData = SRange.getValues();
}

var tss = SpreadsheetApp.openById('0AgcCWQn-aoI1dF96X2dBT2dVVFZ2SU1NRWdYTDJhT2c'); // tss = target spreadsheet Corbin1
var ts = tss.getSheetByName('Sheet1'); // ts = target sheet

//set the target range to the values of the source data
ts.getRange(A1Range).setValues(SData);

//Confirmation message that the row was copied 
Browser.msgBox('You have successfully copied Row: ' + answer);
}

Upvotes: 2

Views: 2281

Answers (1)

br araujo
br araujo

Reputation: 1872

You are getting this error maybe because your spreadsheet does not have a sheet called Steve2 or Cobin1. Try using the method master.getSheets()[0], this way you will get the first sheet without using their name.

You can algo use this piece of code to check the sheets names:

for(var x in master.getSheets()) {
   Logger.log(master.getSheets()[x].getSheetName());
}

best,

Upvotes: 1

Related Questions