Skip to content

Instantly share code, notes, and snippets.

@tanaikech

tanaikech/secretsubmit.md Secret

Created Sep 1, 2017
Embed
What would you like to do?
Retrieving Spreadsheet ID from Range using Google Apps Script

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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.