Retrieving Spreadsheet ID from Range using Google Apps Script

secretsubmit.md

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);
}