How to copy a row from one google spreadsheet to another google spreadsheet using google apps script?

by

NOTE: This solution works for copying rows from one sheet to another sheet in the SAME spreadsheet, and does NOT work for copying a row from a sheet to a DIFFERENT spreadsheet.

Check out the documentation here:

http://code.google.com/googleapps/appsscript/service_spreadsheet.html

Let’s assume you’re working in the spreadsheet where you’re copying from.

You’d have to get a handle to the current spreadsheet and the target spreadsheet. You’ll need to get the ID for the target spreadsheet. Details are in the link up there.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var target = SpreadsheetApp.openById("abc1234567");

Next we need to pick the particular sheets within those spreadsheets. Let’s say your row is on the sheet named “New Stuff”, and you have a sheet in the target spreadsheet named “Archive”.

var source_sheet = ss.getSheetByName("New Stuff");
var target_sheet = target.getSheetByName("Archive");

Now, the concept that google apps spreadsheets use are ranges. A range is just a chunk of cells. So we need to determine the from-range and the to-range. Let’s say your sheet has 7 columns and you want the 10th row.

var source_range = source_sheet.getRange("A10:G10");
var target_range = target_sheet.getRange("A1:G1");

So we’re going to take that row and put it on the first row of the target sheet. Now for the actual copy:

source_range.copyTo(target_range);

And you’re done!

Now, this will always clobber the first row of the target sheet. There’s plenty you can do to stop that. Instead of always using the first line, you could use the sheet object methods to find the last row, add one after, and then use it as your range.

var last_row = target_sheet.getLastRow();
target_sheet.insertRowAfter(last_row);
var target_range = target_sheet.getRange("A"+(last_row+1)+":G"+(last_row+1));

That way each time you copy a row over, it just gets added to the bottom of the sheet.

More Related Contents:

  1. Long processing time likely due to getValue and cell inserts
  2. Why do we use SpreadsheetApp.flush();?
  3. Convert column index into corresponding column letter
  4. How to poll a Google Doc from an add-on
  5. How to read the correct time/duration values from Google Spreadsheet
  6. Google Spreadsheet Script – How to Transpose / Rotate Multi-dimensional Array?
  7. Emailing Google Sheet range (with or without formatting) as a HTML table in a Gmail message
  8. How much faster are arrays than accessing Google Sheets cells within Google Scripts?
  9. Google Sheets add-on: Set anonymous/dynamic functions to Menu using Google App Script
  10. Google Apps Script toast messages don’t appear for anonymous editors
  11. Synchronize independent spreadsheet rows, filled by IMPORTRANGE()
  12. How to speed ​up the search data in sheet
  13. How to Autofill last row with formula, when data is received from IFTTT on the last row?
  14. Scraping data to google sheets from a website that uses JavaScript
  15. How to Transpose / Rotate Multi-dimensional Array?
  16. How to allow access for importrange function via apps script?
  17. How to fix ‘TypeError: Cannot call method “getRange” of null’ in Google Apps Script
  18. How to run certain scripts under the authority of a specific user?
  19. Set anonymous/dynamic functions to Menu
  20. Generate PDF of only one sheet of my spreadsheet
  21. To exceed the ImportXML limit on Google Spreadsheet
  22. How do I replace text in a spreadsheet with Google Apps Script?
  23. How to make onEdit() trigger function apply to multiple sheets
  24. Google apps script: how to persist data in spreadsheet between different function calls?
  25. Google Script: Play Sound when a specific cell change the Value
  26. Failure of calling Google App Script onEdit function many times in a second
  27. Add timestamp to named column in Google Spreadsheet onEdit script
  28. What are some use cases for SpreadsheetApp.flush()?
  29. google script detect empty cell after edit or delete
  30. Converting Google spreadsheet date into a JS Date object?

Leave a Comment