How can I append data from one spreadsheet to another one?

Question

I have this handy script for about a year. Works really well to periodically save data (crypto prices) on multiple worksheets. But my spreadsheet is about to explode, too many cells. So I would like to use multiple spreadsheets. In order to do so I need to mix my script a little but I wasn't able to do it alone.

• Goal : Append a range of values in a different Spreadsheet.

 // [START modifiable parameters]
var rangeToLog = 'Source!A1:B20';
var sheetToLogTo = 'Target';
// [END modifiable parameters]
////////////////////////////////

/**
* Appends a range of values to the end of an archive sheet.
* A timestamp is inserted in column A of each row on the archive sheet.
* All values in rangeToLog go to one row on the archive sheet.
*
* @OnlyCurrentDoc
*/
function appendValuesToArchiveSheet() {
  // version 1.4, written by --Hyde, 30 January 2020
  //  - use Array.prototype.some() to skip empty rows when concating
  //  - see https://support.google.com/docs/thread/27095918?msgid=27148911
  // version 1.3, written by --Hyde, 26 January 2020
  //  - see https://support.google.com/docs/thread/26760916
  var ss = SpreadsheetApp.getActive();
  var valuesToLog = ss.getRange(rangeToLog).getValues();
  var logSheet = ss.getSheetByName(sheetToLogTo);
  if (!logSheet) {
    logSheet = ss.insertSheet(sheetToLogTo);
    logSheet.appendRow(['Date time', 'Data']);
  }
  var rowToAppend = [new Date()].concat(
    valuesToLog.reduce(function concatArrays_(left, right) {
      var arrayContainsData = right.some(function isNonBlanky_(element, index, array) {
        return element !== null && element !== undefined && element !== '';
      });
      return arrayContainsData ? left.concat(right) : left;
    })
  );
  logSheet.appendRow(rowToAppend);
}

Answer 1

Explanation:

There are two ways to get the spreadsheet object of a different spreadsheet from the one bound to the script:

• openById(id):

Opens the spreadsheet with the given ID. A spreadsheet ID can be extracted from its URL. For example, the spreadsheet ID in the URL https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0 is "abc1234567".

• openByUrl(url):

Opens the spreadsheet with the given URL. Throws a scripting exception if the URL does not exist or the user does not have permission to access it.

You can pick whatever suits you the best, but there is no difference in performance. I prefer openById because you pass a "shorter" string (id vs url).

Solution:

Change:

var ss = SpreadsheetApp.getActive();
var valuesToLog = ss.getRange(rangeToLog).getValues();
var logSheet = ss.getSheetByName(sheetToLogTo);

To:

var ss = SpreadsheetApp.getActive(); // source spreadsheet
var ss_t = SpreadsheetApp.openById('id of target spreadsheet'); // target spreadsheet
var valuesToLog = ss.getRange(rangeToLog).getValues();
var logSheet = ss_t.getSheetByName(sheetToLogTo); // logSheet of the target spreadsheet

and logSheet = ss.insertSheet(sheetToLogTo); to logSheet = ss_t.insertSheet(sheetToLogTo);

Full Code:

// [START modifiable parameters]
var rangeToLog = 'Source!A1:B20';
var sheetToLogTo = 'Target';

function appendValuesToArchiveSheet() {

  var ss = SpreadsheetApp.getActive(); // source spreadsheet
  var ss_t = SpreadsheetApp.openById('id of target spreadsheet'); // new code
  var valuesToLog = ss.getRange(rangeToLog).getValues();
  var logSheet = ss_t.getSheetByName(sheetToLogTo); // modified code
  if (!logSheet) {
    logSheet = ss_t.insertSheet(sheetToLogTo); // modified code
    logSheet.appendRow(['Date time', 'Data']);
  }
  var rowToAppend = [new Date()].concat(
    valuesToLog.reduce(function concatArrays_(left, right) {
      var arrayContainsData = right.some(function isNonBlanky_(element, index, array) {
        return element !== null && element !== undefined && element !== '';
      });
      return arrayContainsData ? left.concat(right) : left;
    })
  );
  logSheet.appendRow(rowToAppend);
}