Add a new sheet from template into a raw google script and organize into a folder

Question

I have this spreadsheet and I would like to copy the template in a directory for each month and add the link in each corresponding cell, adding the corresponding data

Now I do it adding new sheets but I wanted to organize by directories for each month.

It's possible?

Thanks

https://docs.google.com/spreadsheets/d/1abdggD73Zb0XmRoFaMx0ssULjtLLTHhFQV3ikbaEK_I/edit?usp=sharing




function newSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var templateSheet = ss.getSheetByName("Plantilla")
    var sheet1 = ss.getSheetByName("Sheet1")
        var getNames = sheet1.getRange("G2:G").getValues().filter(String).toString().split(",");

    for (var i = 0; i < getNames.length; i++) {
        var copy = ss.getSheetByName(getNames[i]);
        if (copy) {
            Logger.log("Sheet already exists");
        } else {
            templateSheet.copyTo(ss).setName(getNames[i]);
            ss.setActiveSheet(ss.getSheetByName(getNames[i]));
            ss.moveActiveSheet(ss.getNumSheets());
        }
    }
}


// function to create the index
function createIndex() {

  // Get all the different sheet IDs
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  var namesArray = sheetNamesIds(sheets);

  var indexSheetNames = namesArray[0];
  var indexSheetIds = namesArray[1];

  // check if sheet called sheet called already exists
  // if no index sheet exists, create one
  if (ss.getSheetByName('index') == null) {

    var indexSheet = ss.insertSheet('Index',0);

  }
  // if sheet called index does exist, prompt user for a different name or option to cancel
  else {

    var indexNewName = Browser.inputBox('The name Index is already being used, please choose a different name:', 'Please choose another name', Browser.Buttons.OK_CANCEL);

    if (indexNewName != 'cancel') {
      var indexSheet = ss.insertSheet(indexNewName,0);
    }
    else {
      Browser.msgBox('No index sheet created');
    }

  }

  // add sheet title, sheet names and hyperlink formulas
  if (indexSheet) {

    printIndex(indexSheet,indexSheetNames,indexSheetIds);

  }

}



// function to update the index, assumes index is the first sheet in the workbook
function updateIndex() {

  // Get all the different sheet IDs
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var indexSheet = sheets[0];

  var namesArray = sheetNamesIds(sheets);

  var indexSheetNames = namesArray[0];
  var indexSheetIds = namesArray[1];

  printIndex(indexSheet,indexSheetNames,indexSheetIds);
}


// function to print out the index
function printIndex(sheet,names,formulas) {

  sheet.clearContents();

  sheet.getRange(1,1).setValue('Workbook Index').setFontWeight('bold');
  sheet.getRange(3,1,names.length,1).setValues(names);
  sheet.getRange(3,2,formulas.length,1).setFormulas(formulas);

}


// function to create array of sheet names and sheet ids
function sheetNamesIds(sheets) {

  var indexSheetNames = [];
  var indexSheetIds = [];

  // create array of sheet names and sheet gids
  sheets.forEach(function(sheet){
    indexSheetNames.push([sheet.getSheetName()]);
    indexSheetIds.push(['=hyperlink("#gid=' 
                        + sheet.getSheetId() 
                        + '";"' 
                        + sheet.getSheetName() 
                        + '")']);
  });

  return [indexSheetNames, indexSheetIds];

}

I have to apologize for my poor English skill. Unfortunately, I cannot understand about copy the template in a directory for each month and add the link in each corresponding cell, adding the corresponding data. Can I ask you about the detail of it? — Tanaike
Create new spreadsheets on all dates in sheet1 and move those new sheets to a folder with the month name (So that no new tabs appear in the main book) And then add a linked index to the created sheets. Sorry I can't explain it better — Juan Zamora
Thank you for replying. Can I ask you about the month name you are using? — Tanaike
on Sheet1 it is organized by months. The idea is to create a sheet on each day of the month and move them to a folder for each of the months on sheet1, in addition to adding an index in one of the columns with a link to each sheet created. It's possible? Now I do it by manually creating a sheet for each month and adding the link manually — Juan Zamora
Just to fully clarify before I post an answer: you want to create 3 folders (1 for each of the months), create a spreadsheet for each of the days mentioned in the sheet and move these spreadsheets to their corresponding month folder. Addditionally, you want to create a link in the original sheet to each newly created spreadsheet. Am I right? — Mateo Randwolf

Mateo Randwolf Mateo Randwolf · Accepted Answer · 2020-04-02T08:48:25

Solution

With the use of the Drive service in Apps Script and Spreadsheet service you can easily achieve your intentions. The following is the self explanatory commented piece of code that achives what you are aiming for.

NOTE : I have only made an example of how it would be with the first month (ENERO) as to achive the rest it would just be copying what I did with the first month.

function myFunction() {
  // Get the sheet where we have all the dates
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  
  // Get the month name. Repeat this for the other months you might want to include
  // For example if February is on A10 just get the range from A10
  var Month1 = sheet.getRange('A1').getValue();
  
  // Create a parent folder in the root of your drive where you will be storing everything for this project
  var parentFolder = DriveApp.createFolder('Months');
  
  // Create a folder for each month using the name we got before. Repeat for the rest of the months.
  var Month1Folder = DriveApp.getFolderById(parentFolder.getId()).createFolder(Month1);
  
  // Get the dates of each month. Repeat for the rest of the months. 
  // For example, if February dates go from A11  to A21 get the range A11:A21
  // Also, getValues returns an array of arrays. Therefore I have flatten it to make a simple 1D array
  var Month1Days = sheet.getRange('A2:A4').getValues().flat();
  
  // For every element of our Month 1 date names array
  // Repeat this block for the rest of the months substituying the array and column (to not overwrite) accordingly 
  for(i=0;i<Month1Days.length;i++){
    
    // Create a new spreadsheet with the name of the dates of that month and get its id
    var idFile = SpreadsheetApp.create(Month1Days[i]).getId();
    
    // Get the file object of the new Spreadsheet
    var file = DriveApp.getFileById(idFile);
    
    // Add this file to our month folder
    Month1Folder.addFile(file);
    
    // Remove the duplicate that is still in the root folder
    DriveApp.getRootFolder().removeFile(file);
    
    // Paste a link to this new spreadsheet in our original sheet in the third column
    sheet.getRange(i+1, 3).setValue(file.getUrl());
  } 
  
}

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)

Add a new sheet from template into a raw google script and organize into a folder

1 Answers

Solution