0

I am trying to build a data logging workflow using Sheets. I've got a getValue/setValue pair that looks great, but isn't writing to the target cell, and I can't understand why. Here's the code I'm using:

function TESTcopy() {

  var srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSht = srcSpreadsheet.getSheets()[0];
  
  var tgtSpreadsheet = SpreadsheetApp.openById('1xjjXG-tK3DIkgJTQbkR6XFhOEP5nhaNfqqXiFyMu0AY');
  var tgtSht = tgtSpreadsheet.getSheets()[0];
  
  var data = srcSht.getRange(4,2,20,18).getValues();

  tgtSht.getRange(345,5,20,18).setValues(data);
  
}

I've gone over the entire script letter-by-letter, and have Googled and searched on SO for several hours. I know that there's a simple explanation, but I just can't see it.

Does anybody else have any ideas? Thanks in advance!

Rubén
  • 34,714
  • 9
  • 70
  • 166
user3279926
  • 59
  • 2
  • 12
  • Try logging the data to console. Also, you could use the `copyTo(destination)` method. [Link](https://developers.google.com/apps-script/reference/spreadsheet/range#copytodestination) – ADW Feb 08 '21 at 09:19
  • 1
    The explanation indeed is that you don't get the right data. – Marios Feb 08 '21 at 09:22
  • Have a look at this thread to understand how getRange works: [Sheet.getRange(1,1,1,12) what does the numbers in bracket specify?](https://stackoverflow.com/questions/11947590/sheet-getrange1-1-1-12-what-does-the-numbers-in-bracket-specify) – Marios Feb 08 '21 at 09:23
  • ADW - Thanks for replying! Question, tho... Isn't copyTo(destination) limited to projects where the source range and the target (destination) range are both on the same sheet? Please correct me if I'm wrong. In this case, the source and target sheets are different sheets in different spreadsheets. – user3279926 Feb 08 '21 at 09:40
  • Maybe I'm looking at this too hard, but I see an attempt to copy a 20 row X 18 column range from spreadsheet A to spreadsheet B. From reading the post referenced, the top-left cell in the source is B4, and the top0left cell in the destination is E345. What I'm having trouble understanding is why the code is either not working, or is pasting blanks when the last two numbers in the getValue statement and the setValue statement are identical – user3279926 Feb 08 '21 at 10:06
  • @user3279926 can you tell me exactly what are the range of cells you are trying to copy? e.g. `A5:C20`. Can you give me this expression? for both source range and target range. And you are right, `copyTo` can be used only within the same spreadsheet, so your approach is correct but you haven't specified correctly the arguments within `getRange`. So please, provide the cell coordinates as I said before, and I will correct the arguments for you. – Marios Feb 08 '21 at 10:21
  • Found the problem, and fixed it. All is right with the world, now. Moral of the story: A problem in your code isn't always a problem in your code... – user3279926 Feb 08 '21 at 10:42

0 Answers0