Google-sheets – Is accessing multiple Google Sheets Files(not multiple google sheets within the same Google Sheet File) in one script possible

google sheetsgoogle-apps-script

I was wondering if I can copy a certain range of cells in Google Sheet File A and paste it to another Google Sheet File B.

More specifically, I'm referring not to sheets in the same Google Sheet(eg: sheet1, sheet2 in Google Sheet A) but 2 completely different Google Sheet Files(eg: Google Sheet File A, Google Sheet File B).

I have been googling it for a while but since Google Sheets and sheets share the same word I've been struggling to find a conclusive answer.

I think you can access different Google Sheets using sheet IDs in the macro but I'm not too sure if I'm on the right track.

Best Answer

Short answer: Yes, it's possible to copy a range from one spreadsheet to another

It's very likely that your googling didn't give you the results that you are looking for because you are not using the terms commonly used: spreadsheet and workbook, actually, the Google Apps Script use spreadsheet for the file and sheet for the sheets that it contains.

You could handle the spreadsheet files by using the Class Spreadsheet from the Spreadsheet Service and Clase File from the Drive Service. Also this could be done by using Advanced Services.

To get an spreadsheet object that represents an spreadsheet file, you could use the id, user or a file object.

Method          Return type     Brief description
------------------------------------------------------------------------------------------------
open(file)      Spreadsheet     Opens the spreadsheet that corresponds to the given File object.
openById(id)    Spreadsheet     Opens the spreadsheet with the given ID.
openByUrl(url)  Spreadsheet     Opens the spreadsheet with the given URL.

References

Related Solutions

Google-sheets – How to apply a script to only one specific sheet within a spreadsheet

There are a couple of ways to do that. First, you need to declare the spreadsheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();

Secondly, declare the first sheet like this:

var sh0 = ss.getSheets()[0];

or like this:

var sh0 = ss.getSheetByName("Robin");

Then set the active cell and retrieve the column index:

var editedCell = sh0.getActiveRange().getColumnIndex();

And do the logic for the sort:

var columnToSortBy = 1;
var tableRange = "a2:I30";

if(editedCell == columnToSortBy){   
  var range = sheet.getRange(tableRange);
  range.sort( { column : columnToSortBy } );
}

Complete code, a bit shortened:

function onEdit(){
  var sh0 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var editedCell = sh0.getActiveRange().getColumnIndex();

  if(editedCell == 1) { 
    var range = sh0.getRange("A2:I30");
    range.sort({column: 1});
  }
}

Google-sheets – How to share different tabs in the same Google Sheet with different people/groups

A solution to this problem is simply creating different spreadsheets, and then having a "summary" spreadsheet that references those other spreadsheets using the method described in this Google forum post.

Best Answer

Related Solutions

Google-sheets – How to apply a script to only one specific sheet within a spreadsheet

Google-sheets – How to share different tabs in the same Google Sheet with different people/groups

Related Topic