3

I'm having columns A, B and C filled with data in Sheet1. Those columns contains data that is static for each new sheet. Currently, I need to manually copy the first three columns to each new sheet I create.

How can I automate the process that each time I click on the + (Add Sheet), to have that columns copied?

UPDATE: I tried to implement a similar solution found here https://stackoverflow.com/questions/55269902/automatically-copy-data-between-google-sheets

Even though this seems like a good starting point, my lack of knowledge on this matter overcomes my efforts. I tried modifying this function a bit, but didn't get any results.

1
  • Welcome. Please add a brief description of your search/research efforts as is suggested on How to Ask (look at the Google Sheets help for macros and Google Apps Script). Sep 18, 2019 at 3:39

3 Answers 3

0

Here's a roadmap to implement this functionality yourself using a simple Google Apps Script Trigger.

Create a trigger onEdit(e) that iterates through all sheets, checking whether there are any empty ones (i.e. newly created). If it none are empty, job done. If it finds an empty one, it reads in columns A, B, C from your first sheet and sets the values of the empty sheet to those. Job done.

Alternatively, you can delete the empty sheet and copy the static sheet.

Yet another way of doing it is to create a new menu item (basically an add-on) that simply creates a new template with the right columns filled in when clicked.

Yes, you'll have to learn basic Apps Script for it, but it's nothing extraordinarily complicated. This should help you get started for how to read and write cells: https://stackoverflow.com/questions/11334296/google-sheet-script-set-cell-value.

0

Would you like to try the IMPORTRANGE function?

IMPORTRANGE - Imports a range of cells from a specified spreadsheet.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/<spreadsheets-id>", "Sheet1!A1:C50")

0

If I May,
I find it helpful when I'm decided about where a specific range is going to stay then I make a Named range defining it.
Then you can work with that data as a variable using what ever name you chose.
The Following can be found in their reference materials
~ As per Google:

Name a range

Name a range of cells! You can name ranges in Google Sheets to keep better track of them and create cleaner formulas.

For example

nstead of using "A1:B2" to describe a range of cells, you could name the range "budget_total." This way, a formula like:

"=SUM(A1:B2, D4:E6)" could be written as "=SUM(budget_total,quarter2)." [![enter image description here][1]][1]

Open a spreadsheet

To do this open a spreadsheet in Google Sheets. Select the cells you want to name.
Click Data and then Named ranges.
A menu will open on the right.

Tip:

To quickly open this menu, in the range box on the top left:

  • click the drop down menu Down arrow Manage named ranges.
  • Type the range name you want. To change the range:
  • click Spreadsheet Grid.
  • Select a range in the spreadsheet or, type the new range into the text box,
  • then click Ok.
  • then Click Done.

Range names:


Can contain only letters, numbers, and underscores.
Can't start with a number, or the words "true" or "false."
Can't contain any spaces or punctuation.
Must be 1–250 characters.
Can't be in either A1 or R1C1 syntax.

For example:

  1. you might get an error if you give your range aname like "A1:B2" or "R1C1:R2C2."

Edit or delete a named range:

  1. Open a spreadsheet in Google Sheets.
  2. Click Data > Named ranges.
  3. On the named range you want to edit or delete, click Edit Edit.
  4. To edit the range, enter a new name or range, then click Done.
  5. To delete the named range, next to the name, click Delete rangeDelete
  6. On the menu that opens, click Remove.

**Note:** When you delete a named range, any formulas that reference it will no longer work. Protected ranges that reference a named range will use the cell values and continue to work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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