CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
Diego Resende
Diego Resende

Reputation: 83

Create a new spreadsheet with data from just one sheet of another spreadsheet

I have the following code that opens only one tab of a spreadsheet:

const sheetName = SpreadsheetApp.getActiveSheet().getName()
const sheetID = SpreadsheetApp.getActive().getId()

const sheet = SpreadsheetApp.openById(sheetID).getSheetByName(sheetName)

I would like to take the data from this tab and create a new spreadsheet with just that data

Upvotes: 0

Views: 143

Answers (2)

CMB
CMB

Reputation: 5163

Alternative:

You can also use copyTo() to copy an entire sheet to another spreadsheet:

function copyTab() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = source.getActiveSheet();
  var destination = SpreadsheetApp.create("New Sheet");
  sheet.copyTo(destination);
  // optional, this deletes the initial Sheet1 in new spreadsheet
  var sheet1 = destination.getSheetByName("Sheet1");
  destination.deleteSheet(sheet1);
}

References:

copyTo() Spreadsheet

Upvotes: 1

Marios
Marios

Reputation: 27350

Explanation:

I believe your goal is to transfer data between a source spreadsheet file (the one you have the script bound to) to a target spreadsheet by its id.

Solution:

In the comments you can find expalanation on what you need to adjust:

function myFunction() {
  const source_ss = SpreadsheetApp.getActive();
  const target_ss = SpreadsheetApp.openById("id"); // put the id of the target spreadsheet
  const source_sheet = source_ss.getSheetByName('Sheet1'); // put the name of the source sheet
  const target_sheet = target_ss.getSheetByName('Sheet1'); // put the name of the target sheet
  const data = source_sheet.getDataRange().getValues(); // take the data of the source sheet
  target_sheet.getRange(1,1,data.length,data[0].length).setValues(data); // paste the data to the target sheet
}

If your goal is to create the target spreadsheet on the fly, then use this code:

function myFunction() {
  const source_ss = SpreadsheetApp.getActive();
  const target_ss = SpreadsheetApp.create("target"); // create a target spreadsheet
  const source_sheet = source_ss.getSheetByName('Sheet1'); // put the name of the source sheet
  const target_sheet = target_ss.getSheetByName('Sheet1'); // only this sheet is available in the target sheet
  const data = source_sheet.getDataRange().getValues(); // take the data of the source sheet
  target_sheet.getRange(1,1,data.length,data[0].length).setValues(data); // paste the data to the target sheet
}

References:

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".

Upvotes: 1

Related Questions