Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.5k views
in Technique[技术] by (71.8m points)

google apps script - Copy a complete list on one spreadsheet to append on the bottom of another spreadsheet

In Google Spreadsheet I would like to take only the values from a complete list on one spreadsheet and append it to the bottom of a list on another spreadsheet. My trouble is that using the the copyValuesToRange() function errors the following:

Target sheet and source range must be on the same spreadsheet.

Here's my current code:

function transferList() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById("0ABCD"); 
  var target_sheet = target.getSheetByName("RFPData");
  var sheet = source.getSheetByName("RFP List");
  var sheet_last_row = sheet.getLastRow() + 1;
  var source_range = sheet.getRange("A2:I"+sheet_last_row);
  var sWidth=source_range.getWidth() + 1;
  var sHeight=source_range.getHeight() + 1;
  var last_row=target_sheet.getLastRow();
  source_range.copyValuesToRange(target_sheet , 1, sWidth, 
    last_row + 1, last_row + sHeight );
}

Any idea how I can get this to work?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

As you've found, copyValuesToRange() is a Range method that affects a Sheet Object that is in the same Spreadsheet Object as the Range. There isn't an atomic method that will copy a range of values to another Spreadsheet, but there are a number of ways you could do it.

Here's one way.

  • From the source sheet, get all the data in one operation, by selecting the complete range of data using getDataRange() and then grabbing all values into a javascript array with getValues().
  • To ignore the first row of headers, use the javascript array method splice().
  • Locate your destination, which is on the target sheet, starting after the last row of data that's currently there, using getLastRow().
  • Write the source data (without the headers) to the destination sheet starting at the next row, using setValues().

Script:

function transferList() {
  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RFP List");
  var sourceData = sourceSheet.getDataRange().getValues();
  sourceData.splice(0,1);  // Remove header
  var targetSS = SpreadsheetApp.openById("0ABCD").getSheetByName("RFPData");
  var targetRangeTop = targetSS.getLastRow(); // Get # rows currently in target
  targetSS.getRange(targetRangeTop+1,1,sourceData.length,sourceData[0].length).setValues(sourceData);
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...