1

sure I had this down but just can't get it right. Had a script set up to get data from one sheet and put it into another one, which I have done but it leaves gaps when copying and I can't figure out how to solve it. I'm sure in the code, its where I have put a question mark, is where the problem lies.

I have tried put i, last, last+1, 10, 12 but none of these work, feel like i'm missing something small to get this right. Below is the code a link to view the sheet if needed (the sheet is just for me to learn from, a basic example if you will).

Thanks in advance and also if the code could be better written, please just let me know as still learning this :) 

function copyInfo() {
  var app = SpreadsheetApp;
  var copySheet = app.getActiveSpreadsheet().getSheetByName("Copy");
  for (var i = 2; i <12; i++) {   
  var getInfo = copySheet.getRange(2,2,i,2).getValues();
  //  get the info from range above - start at row 2 on column 2 (b), get number of rows i , number of columns = 2, b,c 
  var last = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Paste").getLastRow();
  var pasteSheet = app.getActiveSpreadsheet().getSheetByName("Paste");
//  Tell it where you want the info to go to 

  pasteSheet.getRange(last+1,1,?,2).setValues(getInfo);
  var clearIt = copySheet.getRange(2,2,i,2).clearContent();  
// this clears the copy range aka getInfo
  }}

link to sheet

Kos
  • 4,890
  • 9
  • 38
  • 42
Mike K
  • 89
  • 2
  • 2
  • 7

1 Answers1

7

You can copy whole range at once using copyTo, so your function could be rewritten as:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Copy");
  var pasteSheet = ss.getSheetByName("Paste");

  // get source range
  var source = copySheet.getRange(2,2,12,2);
  // get destination range
  var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,2,12,2);

  // copy values to destination range
  source.copyTo(destination);

  // clear source values
  source.clearContent();
}
Kos
  • 4,890
  • 9
  • 38
  • 42
  • Thanks for the help, think I wasn't super clear/ missed bits in first explanation. It needs to copy the info but add it to the last line in the paste page, just not leave gaps. Your method copies the info and paste it, also does the clear but doesn't add it to the next line, so each time it runs it overwrites the info. I can get it working on one column just not two atm, that is what I'm stuck on, thanks for helping so far though :) – Mike K Jul 07 '17 at 09:53
  • Ok, I understand, made the edit so now it will paste values after last line in Paste sheet. – Kos Jul 07 '17 at 09:58
  • That works pretty much spot on :) how can the last code you sent to start at the first last row, currently it misses the first last row, it then works if you run the script again how it should, just first run starts it say on A3 instead of the blank a2. Thanks for all your help – Mike K Jul 07 '17 at 10:38
  • Please check that a2 is actually blank (there is no white spaces etc), also if there is some value in another cell of second row, it is not considered empty by the Google Apps Script. – Kos Jul 07 '17 at 10:46
  • I had some text in d2, that was causing it, sorted now, thank you very much – Mike K Jul 07 '17 at 10:52