1

My Problem

I have a column of data for users to input text, and it is highly expected that they should add a plus or minus before text, which would result in an error.

For example, they are often shown a string like "+1d4+2", which they are expected to copy and type into the cells, which if they do so, would result in an error.

Typical suggestions such as adding an apostrophe before the + or - sign to prevent the generation of a formula would not work in this context, since users are not expected to understand how to interact with this.

What I've Already Tried

Number Formatting of the Range as Plain Text

One suggestion was to format the range as Plain Text, but I did not see that this prevented a +/- from generating a formula.

Apps Script Code

I saw a code that would look extremely useful posted by a user called Sahil Singh on this answer. I followed all the instructions he gave, but when I run the code, it doesn't work because it says 'Column' is not defined. I know how to refer to a range using VBA in Excel, but not in App Scripts.

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function letterToColumn(letter)
{
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++)
  {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

function main() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  Logger.log(data)
  for (var i = 0; i < data.length; i++) {
   for (var j=0;j<data[i].length;j++)
     if(data[i][j]=="#NAME?") {
     var clm=columnToLetter(j+1);
       var rng=clm+String(i+1);
       var range = sheet.getRange(rng);
       var frml = range.getFormula();
       range.setValue("'+"+frml.substring(1));
       Logger.log("sahil#"+frml);
     }
    Logger.log(data[i][j]);
  }
}
5
  • Please clarify exactly what you mean by "when I run the code". The code should be running from a trigger, you shouldn't be running it.
    – Blindspots
    Commented Feb 29 at 1:03
  • Yes, I followed that instruction. Then after it didn't work, I went to the tab where the code was, selected 'Run', and it showed me an error message saying that 'Column' was not defined Commented Feb 29 at 23:09
  • It is intended to be run from an onEdit trigger.
    – Blindspots
    Commented Mar 1 at 4:34
  • As I just explained, I did run it that way, it didn't work. Commented Mar 1 at 23:10
  • 1. Please edit you question to explain what didn't work means when you ran it the right way. 2. Include your script of the version that was run the right way. 3. Include the information from the execution log for the version that was run the right way, so we understand what success messages or error messages were generated. Running it the wrong way and generating error messages associated with that isn't helpful as that isn't the problem you need help with. I don't see any indication in your script that you ran it the right way.
    – Blindspots
    Commented Mar 2 at 4:13

1 Answer 1

0

As you look to be new to Google Apps Script and new to using it to automate Google Sheets, please read thoroughly https://developers.google.com/apps-script/guides/sheets.


From the question

I know how to refer to a range using VBA in Excel, but not in App Scripts.

Google Apps Script uses JavaScript as the programming language. Ref. https://developers.google.com/apps-script/overview.

Note: Please be aware that JavaScript is case-sensitive, so Column is not the same as column. I mention this because the code includes column, not Column. When mentioning an error message, please add the textual error.

The error you are getting is likely related to how you run the script. The code included in the question looks like it should be run by calling the Google Apps Script / JavaScript function main from the Google Apps Script user interface or from Google Sheets using a custom menu, as a recorded macro, among other options.

The functions columToLetter and letterToColumn require a value for the column parameter. If they are called without assigning a number or letter (string), respectively, an error will be thrown.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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