Google Sheets – How to Comment Out Parts of a Formula

formulasgoogle sheets

How can I comment out parts of a formula in a Google Drive Spreadsheet?

In PHP you can comment a line of code using a # what is the syntax to do the same in a Google Spreadsheet?

I want to use this to temporarily disable a part of a formula rather than write a note / comment.

For instance I have this formula:

=QUERY({
'ALPHA'!A6:X70;
'BETA'!A6:X70;
'CHARLIE'!A6:X69
},"select * where Col1 <>''
order by Col24 DESC")

I would like to be able to comment out the part:

 order by Col24 DESC

Best Answer

Use the concatenation operator & and IF

Example

=QUERY({
'ALPHA'!A6:X70;
'BETA'!A6:X70;
'CHARLIE'!A6:X69
},"select * where Col1 <>''"
&IF(FALSE," order by Col24 DESC",))

Explanation

The formula syntax doesn't include something similar to the PHP # comment, JavaScript // comment, /* */ multiple line comment or HTML , but you could use functions like IF. Another alternative is IFERROR. If the formula returns a number, N could be another alternative, as it returns 0 when it's argument isn't a number.

In this case I suggest the use of & and IF because the QUERY's second argument should be a text value. & concatenates the two operands and returns a text value; by using IF we could set a condition and the value to be returned if the condition is true, in this case order by Col24 DESC (please note the leading space), and another value if the condition is false, in this case I suggest to leave the second argument blank, to return a blank.

You could go even further by using a cell reference for the condition to toggle the inclusion/exclusion of the order clause.

Pro-tip: Use Google Apps Script to document and toggle formulas. A cell note or comment could be used to mark those formulas that have being documented this way.

Related Solutions

Google-sheets – How to use Google Scripts w/ spreadsheet and jotform to auto sort so the newest entry is always listed on the top row

Simple triggers like onOpen and onEdit have restrictions.

In contrary of onOpen and onEdit that are reserved words onChange it's not, but its commonly used to name an installable trigger to be called by the spreadsheet change event. In the same way it's possible to set functions to be called ty the the spreadsheet open and change events which not have the restrictions of simple triggers.

We could create simple triggers manually or programmatically.

For further details please read Triggers and Events Guide.

Google Forms – Map Specific Parts to Different Spreadsheets

You can run a script that will catch the submitted responses, then add a row to the public spreadsheet with the appropriate data. If you need any help writing this, let me know.

Edit

Something like this should do it:

/**
 * This will 
 * set a trigger for this as described here: 
 * https://developers.google.com/apps-script/understanding_triggers#ActionTriggers
 * @param {Object} e The event parameter for form submission to a spreadsheet;
 *     see https://developers.google.com/apps-script/understanding_events
 */

function onSubmit(e){
  properties = {publicSheetId:'PUT YOUR PUBLIC SHEET HERE',
                publicFields:['Question 1', 'Question 2?']};
  var form = FormApp.getActiveForm();
  var responses = form.getResponses();
  Logger.log(responses);
  var lastResponse = responses[responses.length -1];
  var publicSheet = SpreadsheetApp.openById(properties.publicSheetId);
  //form = FormApp.getActiveForm();
  //e = FormApp.getActiveForm().getResponse()
  Logger.log(lastResponse.getId());
  var itemResponses = lastResponse.getItemResponses()
  var publicItemResponses = []
  for (var i = 0; i < properties.publicFields.length; i++){
    var publicField = properties.publicFields[i]
    for (var j = 0; j < itemResponses.length; j++) {
      var itemResponse = itemResponses[j];
      Logger.log(itemResponse.getItem().getTitle());
      Logger.log(publicField);
      if(itemResponse.getItem().getTitle() == publicField){
        publicItemResponses.push(itemResponse.getResponse());
        break;
      }
    }
  }
  publicSheet.getSheets()[0].appendRow(publicItemResponses);
}

Best Answer

Related Solutions

Google-sheets – How to use Google Scripts w/ spreadsheet and jotform to auto sort so the newest entry is always listed on the top row

Google Forms – Map Specific Parts to Different Spreadsheets

Related Topic