How to export each google sheet as its own document using google scripts?

Question

This is my first time using javascript, so maybe I am missing something. The task: I have code that, given a table of information, creates new sheets for each unique name in one of the columns. Notice how Josh chan appears in two of these columns:

With a large enough list, it would be tedious to go through 100 sheets. Is there any way to export each google sheet as its own document?

I've looked hard, I haven't found any way to do this. Any help and expertise would be much appreciated. Side note: this might be possible with macros or something like that.

Thanks!

Answer 1

You need to filter your data with regards to the unique names. You also need to manipulate the data so you can easily loop them all and add them onto a new sheet. There is a lot going on but there are ample comments to state what each step does.

Code:

function createSheetPerUniqueName() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  // remove first row as header
  var headers = values.shift();

  // get unique names
  var name = values.map(row => row[1]).filter(function (value, index, self) {
    return self.indexOf(value) === index;
  });

  // traverse unique names
  name.forEach(function (uniqueName) {
    // get all rows with the unique name
    var data = values.filter(row => row[1] == uniqueName);

    // pass the id of destination folder (in between "folders/" and "?resourcekey=")
    var folder = DriveApp.getFolderById("1UvAE1ZbFso35kiooJ7lUq_nDH2QgUYyS");
    // create spreadsheet
    var temporarySheet = SpreadsheetApp.create(uniqueName);
    // move created spreadsheet to folder and get its ID
    var sheetId = DriveApp.getFileById(temporarySheet.getId()).moveTo(folder).getId();

    // access the sheet via ID
    var newSpreadSheet = SpreadsheetApp.openById(sheetId);
    // rename Sheet1 to name
    var newSheet = newSpreadSheet.getSheets()[0];
    newSheet.setName(uniqueName);

    // set header
    newSheet.getRange(1, 1, 1, data[0].length).setValues([headers]);
    // set data
    newSheet.getRange(2, 1, data.length, data[0].length).setValues(data);
  });
}

Sample Data:

Output:

Note:

• For every unique name, there will be a corresponding spreadsheet to be generated in the chosen folder. And all those rows with that name will be added to that new spreadsheet where it belongs.

Resource:

• How to find Folder ID

Answer 2

So if I'm getting this correctly you want the following:

For every unique entry make a new Google Sheets (so a new file, not just a tab)

First things first

You'll have to create a template document which you'll copy every time a new document's made. So for this example, I'll use a sheet called templateSheet as the template, and I'll refer to the main sheet as masterSheet.

Master sheet

Try using this code and filling out the variable templateSheet with the id of your templateSheet

function myFunction() {
  // Template sheet stuff
  // Insert id of template sheet
  const templateSheet = "";
  const templateSheetObj = DriveApp.getFileById(`${templateSheet}`);

  // Main sheet stuff
  const ss = SpreadsheetApp.getActiveSheet();
  const allData = ss.getRange(2, 1, ss.getLastRow() - 1, 6).getValues();
  let usersDone = [];

  for (const entry of allData) {
    const fullName = entry[1];
    if (!usersDone.includes(fullName)) {
      // this makes a copy and sets the title of document to fullname variable
      const copy = templateSheetObj.makeCopy(fullName)
      usersDone.push(fullName);
    }
  }
}

Output

How my Drive folder now looks

Content of new document

Since you have the new sheet object saved in copy you can freely do whatever with the new copy, like adding all the rows of data or anything like that.