Move data from cells in worksheet to another sheet

问题:

I am having a hard time figuring this out. I want to extract data from one worksheet and copy it into another Google Sheet. First, I am getting an error when I try to use getvalues, which has not happened before. Also, I am getting an error when I try to use setValue for my "type" variable. I am trying to take data from "sourceCC" and put it in various cells in "destSS". Any help would be greatly appreciated.

function sendData() {

  var sourceSS = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = sourceSS.getSheetByName("Data");
  var dataLastRow = dataSheet.getLastRow();
  var dataValues = dataLastRow.getRange("A"+(dataLastRow)+":G"+(dataLastRow)).getValues();

  var emailSheet = sourceSS.getSheetByName('Emails');
  var recipient = emailSheet.getRange('B2:B22').getValues();
  var orgUnit = emailSheet.getRange('A2:A22').getValues();


  var destSS = SpreadsheetApp.openById('SHEETID');
  var destSheet = destSS.getSheetByName('Sheet1');
  var destLastRow = destSheet.getLastRow();
  var type = destLastRow[0].setValue('NEW');
  var ID = destLastRow[1];

}

回答1:

In Javascript (and therefore in Google Apps Script), a method can only be invoked if the target object "has" that method. You're getting errors because you've mixed up some of your objects - the object types don't support the methods that you're using. (Agreeing with Serge's comment!)

The errors you have are:

1. In the third line of code, dataLastRow = dataSheet.getLastRow() yields a Number, then you try dataLastRow.getRange(). However, .getRange() is a Sheet method.

2. Later, destLastRow = destSheet.getLastRow() yields another Number. The next line has two errors. The first is that this Number is treated as an Array, destLastRow[0].

3. The second error in that line is invoking Range.setValue() on an undefined value. (It's undefined because the Array indexing is incorrect.)

4. Just below that, var ID = destLastRow[1] repeats error #2.

This edited version removes those errors. It may have other errors in logic, and it's certainly not complete, but it runs. Comments have been added to show what object types are returned with each statement.

function sendData() {

  var sourceSS = SpreadsheetApp.getActiveSpreadsheet();      //= Spreadsheet
  var dataSheet = sourceSS.getSheetByName("Data");           //= Sheet
  var dataLastRow = dataSheet.getLastRow();                  //= Number
  var dataValues = dataSheet.getRange("A"+(dataLastRow)+":G"+(dataLastRow)).getValues();
                                                             //= Array [[]]

  var emailSheet = sourceSS.getSheetByName('Emails');        //= Sheet
  var recipient = emailSheet.getRange('B2:B22').getValues(); //= Array [[]]
  var orgUnit = emailSheet.getRange('A2:A22').getValues();   //= Array [[]]

  var destSS = SpreadsheetApp.openById('SHEETID');           //= Spreadsheet
  var destSheet = destSS.getSheetByName('Sheet1');           //= Sheet
  var destLastRow = destSheet.getLastRow();                  //= Number
  var type = destSheet.getRange(destLastRow, 0);             //= Range
  type.setValue('NEW');                                      //= Range (for chaining)
  var ID = destSheet.getRange(destLastRow, 1);               //= Range

}

---

Autocomplete as a defect removal tool

In this situation, the Autocomplete feature of the Apps Script Editor is your friend. When the editor is able to determine what object type you're dealing with, it will offer a list of supported methods. Here's what you see if you start typing var dataValues = dataSheet.getR...:

As soon as you typed ., the editor offered up a long list of methods. As more characters were typed, the list got shorter - and at this point you see a variety of Sheet.getRange() methods.

On the other hand, here's what happens when we type var dataValues = dataLastRow.:

Not much. The editor has no autocomplete methods to offer for this type, Number. If you're tuned in to this behavior as you edit, you can avoid many simple mistakes that can have a high PITA factor later.