Google Apps Script ➡️ Utils. Copy data values between spreadsheets

The code here is the part of “Utils” project on GitHub.

The basic version of the code:

/**
 * @param {String} [ssIdFrom]
 * @param {String} sheetNameFrom
 * @param {String} [ssIdTo]
 * @param {String} sheetNameTo
 */
function copyDataValuesBetweenSpreadsheets_(ssIdFrom, sheetNameFrom, ssIdTo, sheetNameTo) {
  var ssTo = getSpreadsheetByIdOrActive_(ssIdTo);
  var sheetTo = ssTo.getSheetByName(sheetNameTo);
  var dataFrom = getDataBySpreadsheetIdSheetName_(ssIdFrom, sheetNameFrom);

  sheetTo.clearContents();
  var rangeTo = sheetTo.getRange(1,1,dataFrom.length, dataFrom[0].length);
  rangeTo.setValues(dataFrom);

  return 0;
}


/**
 * @param {String} [ssId]
 * @param {String} sheetName
 * 
 * @returns {Array<Array>} data
 */
function getDataBySpreadsheetIdSheetName_(ssId, sheetName) {
  var ss = getSpreadsheetByIdOrActive_(ssId);
  var s = ss.getSheetByName(sheetName);
  var r = s.getDataRange();
  var d = r.getValues();
  return d;
}

/**
 * @param {String} [ssId]
 * 
 * @returns {SpreadsheetApp.Spreadsheet}
 */
function getSpreadsheetByIdOrActive_(ssId) {
  if (ssId) {
    ss = SpreadsheetApp.openById(ssId);
  } else {
    ss = SpreadsheetApp.getActive();
  }
  return ss;
}

Modify it for your case.

Usage:

function test_copyDataValuesBetweenSpreadsheets() {
  var ssIdFrom = '1ksvERJT19jcRF4rBPCneH1Z5l090ibVle1LKK6cKbSw';
  var sheetNameFrom = 'Sheet1'
  var ssIdTo = '1aYk-7Xg1KKs5Sudw1U8ZHbAplmmMX_UQijzNJarwT_Q';
  var sheetNameTo = 'Sheet1';
  copyDataValuesBetweenSpreadsheets_(ssIdFrom, sheetNameFrom, ssIdTo, sheetNameTo);
}

Use to back up IMPORTRANGE formula:

=iferror(IMPORTRANGE(…), '_data_script_'!A1:Z)

Run every hour. If the importrange formula breaks, the scripting values will show.

Photo by Massimo Botturi on Unsplash