Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm using Google Spreadsheets to manage projects, and I'm not familiar with scripts or macros (though I'd really like to learn how to use it, so if you have any good tutorials or tips, let me know).

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. Thanks for any help!




Here-under a snapshot of the Gantt spreadsheet

enter image description here

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

enter image description here

share|improve this question
To start learning about Google Apps Script go to Extend Google Docs, Sheets, and Forms with Apps Script – Rubén Aug 4 '15 at 15:53
To make external references in Google Sheets, use IMPORTRANGE() – Rubén Aug 4 '15 at 15:54

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.