Google Sheets – Create Data Validation Function to Print Values of a Specified Column Across All Pages

google sheets

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:

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?

Best Answer

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:

Open Tools > Script Editor and put the above code there. Click Save.
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.

Update

If you really want to get the result in A1, then you need to use a script.

Code

// global
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "create URL", functionName: "createURL"}];
  ss.addMenu("URL", menu);
}

function onEdit(e) {
  var activeRange = e.source.getActiveRange();

  if(activeRange.getColumn() == 1) { 
    if(e.value != "") { 
      activeRange.setValue('=HYPERLINK("http://www.example.com/id/'+e.value+'","'+e.value+'")');
    }
  }
}

function createURL() {
  var aCell = ss.getActiveCell(), value = aCell.getValue();
  aCell.setValue('=HYPERLINK("http://www.example.com/id/'+value+'","'+value+'")');  
}

Explained:

The e.value will retrieve the cells value (only applicable a cell). The setValue() will add the concatenated string into the getActiveRange(). All is only executed when e.value contains something and the active range is in column A.

I've created an extra menu option as well, to be able access the script this way.

Example:

I've created an example file for you: onEdit URL builder
Add this script via Tools>Script editor, into the script editor. Press the "bug" button and you can use the script.

Google-sheets – Auto sort data but by sheet not range

The sort method belongs to the class Range, not to the class Sheet. Simply put, it always operates on a range. If you want that range to include all the data in a sheet, use the method getDataRange of the class Sheet.

var range = sh0.getDataRange();
range.sort({column: 2, ascending: false});

For completeness, I present a customizable script for automatic sorting.

  function onEdit() {
    var sheetToSort = "Sheet Name"; // enter name of sheet 
    var columnEdited = 1;           // sort when this column is edited
    var columnSort = 2;             // sort by this column
    var sortAscending = false;      // true or false here

    var sheet = SpreadsheetApp.getActiveSheet();
    var editedCell = sheet.getActiveRange().getColumnIndex();
    if (sheet.getSheetName() == sheetToSort && editedCell == columnEdited) { 
      var range = sheet.getDataRange();
      range.sort({column: columnSort, ascending: sortAscending});
    }
  }