Google-sheets – How to create a Google Spreadsheets whose name and cells auto-complete with cells completed on another sheet

automationgoogle sheetsgoogle-apps-scriptproject-management

I'm using Google Spreadsheets to manage projects, and I'm not familiar with scripts or macros.

What is:

1) I have a Gantt spreadsheet which lists all the ongoing and foreseen project as follows (by column): name of the project, person in charge, beginning and end date (among others columns).

2) Each line corresponds to a specific project, which has its own spreadsheet (which I have to complete again with the information of the Gantt spreadsheet). The URL of each spreadsheet is hyperlinked in the Gantt spreadsheet in the "Name of the project" cell.

3) This project spreadsheet is based on a Template that I have in my Drive. (Therefore, each time I have a new project, I copy this Template and then rename it with the name of the project).

2 challenges:

• Would it be possible to automatically generate a new spreadsheet and rename it with the name of the "Name of the project" cell (in the Gantt spreadsheet)?

Would it even be possible to generate this new project spreadsheet based on the Template?

(It would require a script like: "Get the text of the cell" > "Copy the Template spreadsheet" > "Rename it with the name of the cell" > "Open the Newly created spreadsheet" > "Get the URL of the Newly created spreadsheet" > "Hyperlink the corresponding "Name of the project" cell with this URL".)

• (A bit more complicated) Would it be possible to auto-complete the Newly created spreadsheet with the information completed in the Gantt spreadsheet (person in charge, email, beginning and due date) by copy-pasting the corresponding cells of the project?

(For instance: "Copy the name of the person in charge of the project from the Gantt spreadsheet (maybe with a VLOOKUP(Project;A2:G2;2;FALSE), I don't know…) and paste it in the Newly created spreadsheet in the cell B3." (And same with the other cells to be copied.))

I can send you the links of the sheets if needed to give you more view on it.

______________________________________________

Here-under a snapshot of the Gantt spreadsheet

Gantt spreadsheet

Here-under a snapshot the Template spreadsheet (with the cells to be auto-completed).

Template spreadsheet

Best Answer

I think you should be looking in the direction of using a single form to more or less accomplish both tasks. In other words use the form to populate both the Gantt chart AND the new project sheet.

Related Solutions

Google-sheets – Auto create and name new sheet in Google Spreadsheet

The following piece of script will create a menu item in the spreadsheet named Maintenance. There are three menu option present named:

Add Student one by one, Add Student's at once (FIFO), Add Student's at once (LIFO), Delete Sheets

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Add Student one by one", functionName: "addSheet"},
    {name: "Add Student's at once (FIFO)", functionName: "addSheets"},
    {name: "Add Student's at once (LIFO)", functionName: "addSheetsLIFO"},
    {name: "Delete Sheets", functionName: "delSheets"}
  ];
  ss.addMenu("Maintenance", menuEntries);
}

The next piece of code will create a new sheet, only if all information is entered properly. Once the information is entered, select the student that needs to be added and press Add Student:

function addSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var rData = sh.getRange(row, 1, 1, 3).getValues();

  if (row == 1) {
    ss.toast("This is the header");    
    return
  }

  if(rData[0][0] != null || rData[0][1] != null || rData[0][2] != null) {
    try {      
      ss.insertSheet(rData[0][2]);
      ss.setActiveSheet(ss.getSheets()[0]);
      sh.getRange("D"+(row)).setValue(new Date());
    } catch(e) {
      throw 'This student allready has a sheet. Try another sheet name.';      
    }
  }
}

The third piece of code will create new sheets, based on the sheet names, press Add Student's at once (FIFO):

function addSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [];    
  for(var i=1, len=rData.length; i<len; i++) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The fourth piece of code will create new sheets, based on the sheet names, but by itterating backwards, press Add Student's at once (LIFO):

function addSheetsLIFO() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [], i=rData.length; 
  while(i--) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The last script is a bonus to delete all the created sheets, execpt the first sheet, press Delete Sheets:

function delSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getNumSheets();

  for(var i=shs-1;i>0;i--){
    ss.setActiveSheet(ss.getSheets()[i]);
    ss.deleteActiveSheet();
  }
  ss.setActiveSheet(ss.getSheets()[0]);
  ss.getRange("D2:D").clear();
}

I've prepared an example file, to see things happening: Add Student to New Sheet.

Google-sheets – How to personalize HTML emails using Mail Merge and Google Spreadsheets

Data from a spreadsheet can be inserted into a Gmail draft email (which can contain HTML) by using the Yet Another Mail Merge add-on in Google Sheets.