Retrieving Spreadsheet ID from Range using Google Apps Script
This report has been written by Alexander Ivanov and me.
1. Basic Principle
The first sample script is for retrieving spreadsheet ID from a range using Google Apps Script. I sometimes want to retrieve spreadsheet ID from ranges. In such case, I always use this.
- Range
- -> Retrieve Sheet using
getSheet()
- -> Retrieve Spreadsheet using
getParent()
- -> Retrieve spreadsheet ID
var id = "123456789abcdefg";
var sheet = "Sheet";
var cells = "a1:b10";
var range = SpreadsheetApp.openById(id).getSheetByName(sheet).getRange(cells);
var id = range.getSheet().getParent().getId();
>>> id ---> 123456789abcdefg
2. Applications
We considered about applying this to practical scenes.
1. For onEdit(e) trigger
The active Spreadsheet can be used for retrieving from e.range
at onEdit(e)
.
function onEdit(e) {
Logger.log(e.range.getSheet().getParent())
}
2. Enhanced copyTo()
It can be used as the enhanced copyTo()
.
// Source
var range = "a1:b5";
var ss = SpreadsheetApp.getActiveSpreadsheet();
var srcrange = ss.getActiveSheet().getRange(range);
// Destination
var range = "c1:d5";
var dstid = "### file id ###";
var dst = "### sheet name ###";
var dstrange = SpreadsheetApp.openById(dstid).getSheetByName(dst).getRange(range);
For above script, srcrange.copyTo(dstrange);
returns error. Because copyTo()
cannot be used for copying data to other spreadsheet.
So we propose a following simple method. This method can copy data from Spreadsheet A to Spreadsheet B using the range.
function copyToo(srcrange, dstrange) {
var dstSS = dstrange.getSheet().getParent();
var copiedsheet = srcrange.getSheet().copyTo(dstSS);
copiedsheet.getRange(srcrange.getA1Notation()).copyTo(dstrange);
dstSS.deleteSheet(copiedsheet);
}