Google-sheets – Select range by typing it in, e.g.,“f1:f600”

google sheetsgoogle-apps-script

How do I select ranges by typing in the range value in Google Spreadsheets?

I need to be able to select, say, d3:d1000, by typing in “d3:d1000” somewhere. In other words, what F5 does in Excel. I have discovered named ranges; however, it is very cumbersome to create a single-use named range every time I want to select a one-off range. In Excel, I hit F5, type the range value, hit return, and it’s selected. What’s Google Spreadsheets’ equivalent?

Ranges can be entered fine in various places, including as arguments to functions such as SUM, or when applying conditional formatting via the modal dialog, so it seems very strange not to be able to enter them to perform simple selection.

Best Answer

You would need a google apps script for that. Something like this should work:

function onOpen() {
SpreadsheetApp.getUi() 
  .createMenu('Custom Menu')
  .addItem('Select Range', 'selectRange')
  .addToUi();
  }


function selectRange() {
var ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSheet();
var response = ui.prompt('Enter the range:');
if (response.getSelectedButton() == ui.Button.OK) {
    sheet.setActiveRange(sheet.getRange(response.getResponseText()));
} else {
    ui.alert('Could not set an active range');
}
}

After pasting this in the script editor, run the onOpen function (via the 'play' button in the scripteditor). That should create an extra menu-item in your spreadsheet. Go back in your spreadsheet, find the menu-item 'Custom Menu' and click on 'Select Range'...

Related Solutions

Google Spreadsheets Range – How to Select a Range Programmatically in Google Spreadsheets

A way of doing it is to make another column with

=IF(A2=1,C2,"")

A2 being the active flag and then do the `COUNTIF` on that column.

You could also replace the A2=1 with a date test if you wanted.

Google Sheets – How to Clear a Range of Cells

You can clear only the cells with orange background (color code #ff9900) by getting the background colors of all cells with .getBackgrounds() and then checking them for being orange.

function clearOrange() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  var range = sheet.getDataRange();
  var bgColors = range.getBackgrounds();
  for (var i=0; i<bgColors.length; i++) {
    for (var j=0; j<bgColors[i].length; j++) {
      if (bgColors[i][j] === '#ff9900') {
        range.getCell(i+1,j+1).clearContent();
      }
    }
  }  
}

I knew the color code for orange used in Google sheets because I first ran this code with the "if" statement replaced by Logger.log(bgColors[i][j]);

The reason for (i+1,j+1) is that indices in JavaScript are 0-based while row and column numbers are 1-based.

Hypothetically speaking, if you didn't have orange background as the criterion for clearing, you'd have to specify the ranges explicitly; the script has to know what to clear. You could still shorten the script by putting the ranges in an array, as below.

function clear1() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Blad1');
  var rangesToClear = ['D5:D12', 'D15:D20', 'D22:D30'];
  for (var i=0; i<rangesToClear.length; i++) { 
    sheet.getRange(rangesToClear[i]).clearContent();
  }
}

Best Answer

Related Solutions

Google Spreadsheets Range – How to Select a Range Programmatically in Google Spreadsheets

Google Sheets – How to Clear a Range of Cells

Related Topic