1

I am tired of meaningless spreadsheets. I want to use range names more often. It's a pain to make them by hand. My thought if each column on each sheet is given a range, then most of the pain of range creation is done. In the bulk of cases range would be defined as the rest of the column. E.g.

|  C   |
  Cost
   2.75
   3.10

Then the script/add-on would create

Rangename Cost Range C2:C

I would like a script or Google Sheet addon that would in essence

Foreach sheet {
   Foreach Column
       Create Named-Range Column-Name using Column:1 for name
  }
}

Ranges have to be globally unique. So if Cost is a column name on multiple sheets, a mechanism to make it unique needs to be devised.

  • User is responsible for making unique column names. Script behaviour undefined if a duplicate is found.

  • Script prepends sheetname to column name. This produces long names.

  • Script prepends some abstraction of sheet name. Removal of all lower case letters comes to mind. E.g. Inv_Summary becomes I_S- This still might produce collisions.

The best option I think would be to require the script to point out collisions, and exit.

Need a way to maintain them. You add columns, rename columns. Rerun the script updates the names AND their use in formulas throughout the sheet. Internally Sheets is capable of handling a range rename. I don't know if there is an API for this.

Like a way to substitute range names for C:R references in existing sheets. I think this is a separate issue.


What have I tried:

A: search on Google product groups. This provided no joy.

B: google: Automatically create range names. http://www.k2e.com/tech-update/tips/418-tip-fastest-way-to-create-defined-names-in-excel This article shows how it works in excel.

  • Questions about the right SE site for a question should be made on Meta Stack Exchange and regarding if a question is on topic on a specific site and how to ask should be made on the related meta site, in this case Web Applications Meta. Anyway, this questions about Google Sheets could be made on this site as it's a web application but also could be make on Stack Overflow when the question is about programming (writing formulas is also programming). As the question is written it could be slightly too broad and could be improved by adding more details about what your research efforts like searching the add-ons gallery. – Rubén Nov 26 '16 at 22:01
  • Th question looks better, so I upvoted it, but it's too broad to post an specific answer, so I posted a partial answer that could help you to refine your question. I think that it will help you to start writing your first (?) script, learn the Google Sheets - Google Apps Scripts argot and to find the specific class that it's required. – Rubén Nov 27 '16 at 16:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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