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

Max Makhrov
1 min readAug 9

--

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

--

--

Max Makhrov

Google Sheets Developer, master of Online Accounting