If you keep data in separate Google Sheets, copy a range of data from one spreadsheet to another with the IMPORTRANGE function. For example, you may track quarterly sales data for a product in a different spreadsheet for each region. To combine all that quarterly sales data, copy the data from each region's spreadsheet into a single spreadsheet using IMPORTRANGE. |
Use the IMPORTRANGE function
- In Sheets, open a spreadsheet.
- In an empty cell, enter =IMPORTRANGE.
- In parenthesis, add the following specifications in quotation marks and separated by a comma*:
- The URL of the spreadsheet in Sheets.
- The sheet name (optional) and the range of cells to import.
*Note: If you are in a Spanish-speaking country, use the semicolon as a separator instead of the comma.
- Press Enter.
- Click Allow access to connect the 2 spreadsheets.
For example:
To import cells A1 through C10 from sheet 1 of the abcd123abcd123 spreadsheet, you enter: =IMPORTRANGE("//docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10")
Was this helpful?
How can we improve it?
The cloneGoogleSheet() function will copy data (all rows and columns, but no formatting styles) from one Google Spreadsheet to any other Google Spreadsheet under the same Google Drive.
You need specify the file IDs of the source and destination Google Spreadsheets as arguments in the formula and also change the source and target sheet names inside the method body.
This function can be invoked via a time-based trigger or run it manually from the Apps Script editor. However, if you would like to keep the two spreadsheet in sync with each other always, you an consider using the IMPORTRANGE() Google formula that automatically imports a range of cells from a specified spreadsheet into the currently selected cell / range /sheet.
// copy data from Google Sheet A to Google Sheet B // Credit: @chrislkeller function cloneGoogleSheet(ssA, ssB) { // source doc var sss = SpreadsheetApp.openById(ssA); // source sheet var ss = sss.getSheetByName('Source spreadsheet'); // Get full range of data var SRange = ss.getDataRange(); // get A1 notation identifying the range var A1Range = SRange.getA1Notation(); // get the data values in range var SData = SRange.getValues(); // target spreadsheet var tss = SpreadsheetApp.openById(ssB); // target sheet var ts = tss.getSheetByName('Target Spreadsheet'); // Clear the Google Sheet before copy ts.clear({ contentsOnly: true }); // set the target range to the values of the source data ts.getRange(A1Range).setValues(SData); }
Copies a single sheet from a
spreadsheet to another spreadsheet. Returns the properties of the newly created sheet. POST //sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/sheets/{sheetId}:copyTo The URL uses gRPC Transcoding syntax. string The ID of the spreadsheet containing the sheet to copy. integer The ID of the sheet to copy. The request body contains data with the following structure: string The ID of the spreadsheet to copy the sheet to. If successful, the response body contains an instance of SheetProperties. Requires one of the following OAuth scopes: For more information, see the OAuth 2.0
Overview.HTTP request
Path parameters
Parameters spreadsheetId
sheetId
Request body
JSON representation {
"destinationSpreadsheetId": string
}
Fields destinationSpreadsheetId
Response body