3

I have a specific task that I believe might require some kind of custom script.

Here is (a copy) of the spreadsheet in question: https://docs.google.com/spreadsheets/d/1fwk-I3VHIeQP4AC4J-vQB8vbuM3YONgCfTTSrDSNhz8/edit#gid=850165930

This spreadsheet is a template for designing the content model of a piece of software. As such, users will be copying the Sheet entitled "---" depending on the configuration of their project.

My question refers to Column C across all of these pages, entitled "Bound Values". I need some way for all variables within this column (across all pages) to be printed within a single column on a hidden sheet (represented by the sheet titled "🌴").

End Goal

Whenever editing cells in the "Bound Values" column, I would like to have the option of selecting previously used variables within this column across other pages.

I can currently only do this by referencing a single, static sheet that I would need to manually update (which is time consuming).


Another way of looking at it

I would use this on the hidden sheet to scrape all cell values across all pages in column C:

example screenshot .

My Questions Are:

  • How might one be able to do this in Google Sheets, if at all?
  • If not, how might a script be structured to achieve this?
2

If the names of sheets were known, you'd be able to do something like

={filter(Sheet1!C2:C, len(Sheet1!C2:C)); filter(Sheet2!C2:C, len(Sheet2!C2:C)); ... }

to join all of those together.

But since new Sheets will be added, and you don't know what names they will have, you need a script. I can think of two approaches: (a) the script will collect the data from C-columns itself; (b) the script will update the formula (of the above form) that will do the rest. The second one seems easier to code so I'll go with that.

function updateFormula(e) {
  if (e.changeType === 'INSERT_GRID' || e.changeType === 'REMOVE_GRID') {
    var sheets = e.source.getSheets();
    var formulaBits = [];
    for (var i = 0; i < sheets.length; i++) {
      if (sheets[i].getSheetName() !== '🌴') {
        var quotedName = "'"+sheets[i].getSheetName()+"'";
        formulaBits.push('iferror(filter('+quotedName+'!C2:C, len('+quotedName+'!C2:C)))');
      }
    }
    var formula = '={'+formulaBits.join('; ')+'}';
    e.source.getSheetByName('🌴').getRange('C1').setFormula(formula);
  }
}

To deploy:

  1. Open Tools > Script Editor and put the above code there. Click Save.
  2. Go to Resources > "This project's triggers" and create a trigger with parameters "from spreadsheet", "on change".

You may want to add a sheet yourself to make the script run the first time; after that it will update the formula in cell C1 of sheet 🌴 whenever a new sheet is added or a sheet is removed.

In case of renamed sheets, the software takes care of renaming itself, adjusting the formula automatically.

  • Wow, thanks so much for this! Unfortunately, I'm getting an error on line two whenever I try to run it. TypeError: Cannot read property "changeType" from undefined. (line 2, file "boundvalue-watcher") Thoughts? – Luis Queral Jan 29 '16 at 23:31
  • Right, because there is no event object when it's run manually. I changed my suggestion to: "add a sheet to make it run the first time". – user79865 Jan 29 '16 at 23:33
  • It worked! Holy cow, thank you! One more thing: For those unfamiliar with how scripting Google Sheets works, can you confirm the exact steps needed to successfully deploy the script after entering it in the script editor and setting the trigger settings?I felt like I clicked around the various publishing and deploying options after it ran unsuccessfully a few times. Or was that because it just takes a little time to get going after creating a new sheet? – Luis Queral Jan 30 '16 at 19:00
  • I added more detailed instructions. – user79865 Jan 30 '16 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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