How to use the and and or functions in google sheets

Ben Stockton is a freelance tech writer from the United Kingdom. In a past life, he was a UK college lecturer, training teens and adults. Since leaving the classroom, he’s been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloudwards.net. He has a degree in History and a postgraduate qualification in Computing. Read more.

If you’ve ever wanted to check whether data from your Google Sheets spreadsheet meets certain criteria, you can use AND and OR. These logical functions give you TRUE and FALSE responses, which you can use to sort through your data.

If you use AND with multiple arguments, all of those arguments must be true to get a TRUE response; otherwise, AND responds with FALSE. If you use OR, only one argument has to be true for OR to provide a TRUE response.

You can use AND and OR separately or within other functions, like IF.

Using the AND Function

You can use the AND function on its own or combined with other functions to provide a logical (TRUE or FALSE) test.

To get started, open a Google Sheets spreadsheet and click an empty cell. Type =AND(Argument A, Argument B) and replace each argument with the criteria you want to use. You can use as many arguments as you’d like, but you must have at least one for AND to work.

In the example below, we used three arguments. The first argument is the simple calculation of 1+1=2.

The second argues that the cell E3 equals the number 17.

Finally, the third argues that the value of cell F3 (which is 3) is equal to the calculation of 4-1.

As all three arguments are true, the AND formula responds with TRUE in cell A2. If we change any of these arguments, it will cause the AND formula in A2 to change the response from TRUE to FALSE.

In the example below, the AND formula in cell A3 has two correct arguments and one that’s incorrect (F3=10, while F3 actually equals 3). This causes AND to respond with FALSE.

Using the OR Function

While AND requires all the arguments it uses to be true, the OR function only requires one to be true for OR to respond with TRUE.

Like AND, you can use the OR function on its own or combine it with other functions. The same as with AND, you can use as many arguments as you want, but you must have at least one for it to work.

To use OR, click an empty cell and type =OR(Argument A, Argument B) , and replace the arguments with your own.

In the example below, the formula using OR in cell A2 has one incorrect argument out of three (F3=10, where F3 actually equals 3).

Unlike when you use AND, one incorrect argument out of three will result in a TRUE result. For a FALSE result, all the arguments you use must be incorrect.

In the example below, the OR formulas in cells A4 and A5 returned a FALSE response because all three arguments in both formulas are incorrect.

Using AND and OR with IF

Because AND and OR are logical functions with TRUE and FALSE responses, you can also use them with IF. When you use IF, if an argument is TRUE, it returns one value; otherwise, it returns another value.

The format of a formula using IF is =IF(Argument, Value IF TRUE, Value IF FALSE) . For example, as shown below, =IF(E2=1,3,4) causes IF to return the number 3 if cell E2 equals 1; otherwise, it returns the number 4.

Because IF only supports a single argument, you can use AND and OR to introduce complex logical tests with multiple arguments.

Using AND with IF

To use AND within an IF formula, type =IF(AND(AND Argument 1), Value IF TRUE, Value IF FALSE) , and replace your AND argument (or arguments), and IF TRUE and IF FALSE values.

In our example below, we used IF with a nested AND formula in cell A2 with four arguments. All four arguments are correct, so the IF TRUE value (in this case, “Yes,”) is returned.

In cell A3, a similar IF with AND formula contains two incorrect arguments. As AND requires all arguments to be correct, IF returns the IF FALSE value, which is a different text value (“No”).

Using OR with IF

The same as with AND, you can also use OR with IF to create a complex logical test. Only one OR argument has to be correct for IF to return a TRUE response.

To use OR with IF, click an empty cell and type =IF(OR(OR Argument 1), Value IF TRUE, Value IF FALSE) .

Replace the OR argument (or arguments), and your IF TRUE/FALSE values, as required.

In our examples below, the two IF with OR formulas in cells A2 and A3 returned the IF TRUE text value (“Yes”). All four arguments are correct in the A2 IF with OR formula, while A3 has two incorrect arguments out of four.

In cell A4, all four arguments in the IF with OR formula are incorrect. This is why the overall IF formula returns the IF FALSE text value (“No”), instead.

Using the If( ) formula for logical functions

• Tweet
• Share
• Email

What to Know

• The syntax is =if(test, then_true, otherwise_value).
• There are three arguments in the Google Sheets If( ) function: Test, Then_true, and Otherwise-Value.
• In Google Sheets the If( ) statement is entered by typing into a cell; a suggestion box appears to help.

This article explains how to use the Google Sheets If() function. Instructions apply to any current browser and the Sheets app.

The Purpose of the Google If ( ) Function

The If( ) function tests whether a certain condition in a cell is true or false.

• If the condition is true, the function will carry out one operation.
• If the condition is false, the function will carry out a different operation.

The initial true or false test, as well as the follow up operations, are set with the function’s arguments.

Nest If( ) statements to test several conditions and to carry out different operations depending on the outcome of the tests.

The If( ) Function’s Syntax and Arguments

A function’s syntax refers to the format in which the function must be stated. It includes the function’s name, brackets, comma separators, and arguments.

The syntax for the If( ) function is:

=if(test, then_true, otherwise_value)

The function’s three arguments are:

• Test: a value or expression that is tested to see if it is true or false
• Then_true: the operation that is carried out if the test is true
• Otherwise_value: the operation that is carried out if the test is false

The otherwise_value argument is optional, but you must specify the first two arguments for the function to process correctly.

Example of the Google Sheets If( ) Function

In row 3, the If() function returns various results such as:

• Tests to see if the value in cell A2 is equal to 200 (the test argument)
• If it does, the function displays the value 1 in cell B3 (the then_true argument)
• If A1 does not equal 200, the function displays the value 2 in cell B3 (the optional otherwise_value argument)

If you decline to enter an otherwise_value argument, Google Sheets will return the logical value false.

How to Enter the If( ) Function

Unlike Excel, Google Sheets does not use dialog boxes for function arguments. Instead, it has an auto-suggest box that pops up as you type the name of the function into a cell.

To enter the function:

Click cell B3 to make it the active cell.

Type the equal sign ( = ) followed by the name of the function if.

As you type, the auto-suggest box appears with the names of functions that begin with the letter I.

When IF appears in the box, click it to enter the function name and opening parenthesis or round bracket into cell B3.

Click cell A2 in the worksheet to enter that cell reference.

After the cell reference, type the equal symbol (=) followed by the number 200.

Enter a comma to complete the test argument.

Type 2 followed by a comma to enter this number as the then_true argument.

Type 1 to enter this number as the otherwise_value argument. Do not enter a comma.

Press Enter to insert a closing parenthesis ) and to complete the function.

The value 1 should appear in cell B3, given that the value in A2 does not equal 200.

If you click cell B3, the complete function appears in the formula bar above the worksheet.

If you work for an international company, you might need to communicate in various languages. If you are not familiar with a language, this can be challenging. Fortunately, in Google Sheets, the Google Translate function lets users translate text within cells or ranges of cells without leaving the worksheet.

It’s a built-in Google Formula, so you don’t need a browser extension or Google Sheets add-on. Here’s how to use a Google Translate formula in your worksheets to translate text.

The Formula for Google Translate

Take a look at the syntax and arguments of the Google Translate function.

The Google Translate formula contains three arguments, as shown above. The first one is mandatory, while the other two are optional.

1. Text: The argument refers to the text you want to translate. You can either place the text directly in quotation marks in the formula or refer to the cell in Google Sheets containing the text.
2. Source_Language: This is an optional argument that consists of two words code. It refers to the language in which the text currently is. Google Sheet is smart enough to understand the source language on its own if you set this argument as auto. You should, however, specify the source language if you know it. Otherwise, there is no harm in setting it at auto.
3. Target_Language: This is also an optional argument that refers to the language you want the text to be translated into. You should specify the argument if you intend to translate text in a language other than your default language. If this option is set to auto, the Google Sheet will translate text into the language set on your PC by default.

Although the last two options are optional, it’s good practice to specify them to avoid any errors or complexities. If you are not familiar with any of the source or target languages, you can set the arguments as auto.

However, if you don’t know the abbreviations for languages you are translating, you can check them out from the Wikipedia list of language codes.

Before the implementation phase, you should first copy any language code that you plan to either convert from or to. This will save you time when defining arguments in the function.

Among the language codes, English is “en”, German is “de”, and Italian is “it”. Note down the language codes, and let’s put the Google Translate function into action.

Translating the Text in Google Sheets

In the following four cells, A2 to A5, you can see samples of English text which we want to translate from English to German.

1. Go to Cell B2.

2. Add in the Google Translate function.

3. Reference cell A2 as the first text argument.

4. Set source language to be “en”.

5. Set target language to be “de”.

6. Press Enter to execute the formula.

As you can see, the English text has been translated into German. Using the auto-filling sequence function, drag down to translate the text in other cells down the row.

If you forget the function arguments, you can tap the question mark to see the formula help.

The formula help will open up the function syntax and an example showing formula implementation that you can use as a reference.

Related: How to Use Filter Views in Google Sheets

Now let’s implement the Google Translate formula by setting either the source language or target language arguments to auto and keeping both arguments as auto in another.

Making Google Translate Formula Auto-Configurable

Let’s convert the German language back into English by keeping the source language auto.

1. Go to Cell D2.

2. Set cell B2 as a reference for text argument.

3. Add “auto” as the source_language argument.

4. Set target language argument to be “en”.

5. Press Enter to execute the formula.

Google Sheets has smartly picked up the source language and converted it into our target language.

After being translated into German, the original text has been translated back into English slightly differently. Even though the text may not translate with exact synonyms, it’ll make sense in any language.

Populate the other cells down the row.

Let’s set both arguments to auto to see how it affects the final output.

With Both Language Arguments Set to Auto

Let’s convert the text to the computer’s default language by leaving both source and target language arguments set to auto.

1. Add Google Translate formula in cell E2.

2. Choose cell C2 as a reference.

3. Set both language arguments as auto.

4. Press Enter.

In this case, Google Sheets has automatically translated the text from its source language into English. As a default language on our computer, English is the target language.

If you set a default language other than English on your computer, Google Sheets will use that as the target language.

Related: How to Share Your Google Sheets With Others

Translate Google Sheets With Google Translate Function

Translating text in Google Sheets is made easy with the help of Google Translate. By using this function, you can translate worksheets without the use of third-party programs.

Google Translate’s direct access to the Google database significantly reduces the chances of error.

Use Google Sheets with ease thanks to these keyboard shortcuts for Windows and Mac, available as a free cheat sheet PDF.

About The Author

Shan Abdul
(24 Articles Published)

Shan Abdul is a Mechanical Engineering Graduate. After completing his education, he has started his career as a freelance writer. He writes about using different tools and software to help people to be more productive as a student or professional. In his spare time, he loves to watch Youtube videos on productivity.

More
From Shan Abdul

Subscribe To Our Newsletter

Join our newsletter for tech tips, reviews, free ebooks, and exclusive deals!

One More Step…!

Please confirm your email address in the email we just sent you.

If you’re familiar with Google Sheets, you’ve probably used functions such as SUM, AVERAGE, COUNT, etc. in formulas.

Using Apps Script, you can also write your own functions. These are called custom functions. Once you create these functions, you can use them in Sheets just like any other function.

What is a function?

A function is a piece of code that has a name. You can run (or execute) this piece of code elsewhere in your program by using its name. This enables reuse of code. Instead of typing the same code in multiple places, you can define a function in one place and then run it from other places in your program.

Functions in Apps Script have the following structure.

The words function and return are called keywords.

Keywords and reserved words in Apps Script

In Apps Script, there are a few words that have a special meaning. These words are called keywords. You can see the full list of keywords here.

You don’t need to memorize this list. Just remember that Apps Script places some restrictions on these words. For example, you cannot use a keyword as the name of a function.

Keywords are a subset of a larger set of words called reserved words. You also cannot use a reserved word as the name of a function.

The keyword function tells Apps Script that you are about to define a function. A function can accept input values and it can also return an output value. The return keyword tells Apps Script that you’re about to return a value from the function. A function can only return a maximum of one value but you can have functions that don’t return any value.

If the function accepts multiple input values, use a comma to separate them.

The line in the above code that starts with // is a comment. Apps Script ignores comments. They are used to help others understand your code.

Here is a diagram that visualizes what the function is doing.

Statements in Apps Script

A statement is a line of code in Apps Script that does something. Statements usually end with a semicolon. In the previous para, by “return statement”, I meant the following line of code: return outputValue; .

There are functions that don’t need input values. Similarly, you can have functions that don’t return any value. It all depends on what the function does.

A function that does not accept any input value but returns an outputValue.

A function that neither accepts input values nor returns any value.

Naming your function

You must follow certain rules while naming your function.

• Function names can only contain letters, numbers, the dollar sign ($) and underscores.
• Function names cannot be a keyword.
• Function names cannot start with a number.

Remember that function names in Apps Script are case-sensitive. So, MYFUNCTION is not the same as myFunction.

Here are some best practices for naming your function.

• Use a descriptive name to help future readers of your code understand what the function does.
• If the name is made up of several words, either use camel case or use underscores to separate the words. If you choose to separate the words using underscores, ensure that the words are in lower case.

Writing your first custom function

You’re going to write a function that returns a number that you believe will bring you luck. Let’s start by documenting a few details.

• What the function should do: It will return a number.
• Function name: Pick a descriptive name (eg. myLuckyNumber).
• Input values: This function does not need any input values.
• Output value: A number (your lucky number).

I think the number 3 brings me luck so here is a function that returns my lucky number.

Here is a screenshot of the above function in the Apps Script editor.

You can now use this custom function in a Google Sheets formula just like any other function.

You should see your lucky number displayed in the cell where you entered the formula.

I’d appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!

INTRODUCTION

TEXT FUNCTION is found under the TEXT FUNCTION CATEGORY of GOOGLE SHEETS.

TEXT FUNCTION is specially created to format the values and make them more readable as per our requirement.

TEXT FUNCTION converts the number into text , changes the format of the number and gives it custom format as per the code passed to the function.

This function is useful at many places such as handling date and time. Removing time from the date or removing date from the long date etc.
Text function can also be used to combine text and number with any symbols etc.

PURPOSE OF TEXT FUNCTION IN GOOGLE SHEETS

TEXT FUNCTION allows us to format our number into more readable format. There are several standard codes by the use of which we can customize the format for better readability.

Simply saying it is just the way to change the format of the cell just like we change the format using the CHANGE FORMAT DIALOG BOX.

PREREQUISITES TO LEARN TEXT FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

• Basic understanding of how to use a formula or function.
• Basic understanding of rows and columns in Excel.
• A basic understanding of the formats in google sheets will be helpful.
• Of course, Excel software.

SYNTAX: TEXT FUNCTION IN GOOGLE SHEETS

The Syntax for the TEXT function in Excel is

=TEXT ( VALUE TO BE FORMATTED , FORMAT CODE WHICH IS TO BE APPLIED )

VALUE TO BE FORMATTED This is the value on which we want to operate and change the format. It can be a number, date or time.

FORMAT CODE is the code of the format which is to be applied on the given value.

DIFFERENT FORMATS IN TEXT FUNCTION

In simple words, TEXT FUNCTION changes the format of the cells. We can use any format using the Text function and perform various functions such as

• applying leading zeros,
• putting the comma after a fixed number of digits for currency
• extracting date and time from a long date.
• converting the format of date or time
• creating any special phone numbers and many more.

We’ll try them in the example discussed below.

CONCEPT OF TEXT FUNCTION IN GOOGLE SHEETS

The concept of text function is simple.

As the first argument, we have the value on which we want to operate. As the second argument , we tell the TEXT FUNCTION, in which format we want to see the value.

Read the following statement twice.

THE NUMBERS CAN BE DENOTED BY A 0 , # OR ? FOR TELLING THE TEXT FUNCTION ABOUT THE FORMAT WE WANT.

THE TIME AND DATE RECOGNIZES HH FOR HOURS IN NUMBERS , MM FOR MINUTES [NUMBER ], SS FOR SECONDS [ NUMBER ], MM FOR MONTHS DD FOR DATE AND YY OR YYYY FOR YEARS. [ Exhaustive list is given below ]

• d for the day of the month as a single digit [ 1,2. 30,31 ] .
• dd for the day of the month as double digits [ 01, 02. 30, 31 ] .
• ddd for the short name of the day [ Mon, Tue, Wed..] .
• dddd for the full name of the day of the week. [Monday, Tuesday,….Sunday ].
• m for the month of the year as a single digit [ 1, 2 ,…. 12]. The same letter ‘m’ is used for minutes also if it is used with hh or ss as time. [ hh is for hours and ss is for seconds ].
• mm for the month of the year as two digits [ 01, 02, …. 12 ] The same code ‘mm’ is used for minutes also if used with hh or ss as time.
• mmm for the short name of the month of the year. [ Jan, Feb, Mar….]
• mmmm for the full name of the month of the year. [ January, February…]
• mmmmm for the first letter in the month of the year. [ J for JANUARY, F for FEBRUARY and so on ]
• yy for the year as two digits. [ 19, 20, 21 …]
• yyyy for the year as four digits. [ 2020, 2021 … ]
• HH for the hour on a 24-hour clock. [ two digits of the hour 11, 12,…]
• hh for the hour on a 12-hour clock. [ two digits of the hour 01, 02…]
• ss for the seconds in a time.
• ss.000 for milliseconds in a time.
• AM/PM for displaying hours based on a 12-hour clock and showing AM or PM depending on the time of day.

Keeping these points in mind let us try to put this theory into action.

Suppose we have a 9 (Nine) number digit say 1234546789 and we want to put a hyphen (-) at position after 2 digits and 6 digits from the right. Let us create format for this.

We’ll use the formula

=TEXT(123456789, “000-0000-00”) or

All of them would give the result as 123-4567-89

But there is one difference in all the three when it comes to show the significant and non significant digits.

# IGNORES THE NON SIGNIFICANT ZEROES. IF ANY 0 IS AT THE STARTING OR END OF THE NUMBER, IT’LL BE REMOVED.

0 HONORS THE NON SIGNIFICANT ZEROES. IF THE 0 IS AT THE STARTING OR AT THE END OF THE NUMBER [ AFTER THE DECIMAL] IT’LL BE RETAINED.

Similarly , we can try any format. For the help we can use the FORMAT DIALOG BOX TOO. which can be opened easily by CTRL+1 or RIGHT CLICK>FORMAT CELLS.

TEXT FUNCTION IN GOOGLE SHEETS

EXAMPLES :TEXT FUNCTION IN GOOGLE SHEETS

DATA SAMPLE

Let us try to convert various formats using the TEXT FUNCTION.

EXAMPLES SHOWING THE USAGE OF TEXT FUNCTION IN GOOGLE SHEETS

STEPS TO USE TEXT FUNCTION

Many examples has been shown above to use the TEXT FUNCTION.

The generalized steps are following.

• Place the cursor where you want to get the output.
• Put the formula =Text(Value, “Format in the way you want the output”).
• The answer would appear.

For the reference , the above examples can be taken for help.

Investing for developers

If you are looking to use a quick and easy tool for your financial journey, and don’t have time to learn a whole new programming language, then GOOGLEFINANCE functions with Google Sheets are right for you!

The first thing you should probably do is read this page from Google Docs on how GOOGLEFINANCE() functions work.

The most basic one, of course, is =GOOGLEFINANCE(“symbol”), which literally retrieves the latest price of the stock symbol. As you will note right away in the Google Sheet, this data can be delayed up to 20 minutes. So, if you need real-time data, you might have to look elsewhere. You can still import that data into Google Sheets, but you will need to find an outside source for it. You can read my other post about where to get stock data from APIs.

Which leads to the next point, retrieving data from an external source (such as an API). To this, you with actually need to use the IMPORTHTML function. This one a little bit more advanced than simply putting in a symbol like the GOOGLEFINANCE function, but it gives you a lot more flexibility. More on that another day.

So, the best way to start experimenting with this functionality, is by creating a column of “Stocks” like this:

Then, in the next column, enter =GOOGLEFINANCE(A1), then copy and paste to the other rows:

That’s it! It’s like magic!

Of course, this is pretty basic, and you can probably get this functionality on any stock market site that has watchlists like Yahoo Finance. However, the advantage here is that YOU have control over your list of stocks, and you can plug in any other data you need, and run all sorts of crazy calculations and analysis. Then, you can export it, import it, generate charts, graphs, etc. All this is saved to your Google Drive Sheets account, and it can be shared with others or downloaded. Other websites don’t always have this functionality or flexibility.

Many investors use this basic functionality to start building their Sheets, which helps them organize their investments and get a much bigger picture in order to make decisions. Start with this, then expand out. And then, if you’re ambitious, start connecting other applications and APIs to really get some detailed analysis going!

Google Sheets lets you record macros that duplicate a specific series of UI interactions that you define. Once you’ve recorded a macro, you can link it to a keyboard shortcut in the form Ctrl+Alt+Shift+Number . You can use that shortcut to quickly execute the exact macro steps again, typically in a different place or on different data. You can also activate the macro from the Google Sheets Tools > Macros menu.

When you record a macro, Google Sheets automatically creates an Apps Script function (the macro function) that replicates the macro steps. The macro function is added to an Apps Script project bound to the sheet, in a file titled macros.gs . In the event that there is already a project file bound to the sheet with that name, the macro function is appended to it. Google Sheets also automatically updates the script project manifest, recording the name and keyboard shortcut assigned to the macro.

Since every recorded macro is defined entirely within Apps Script, you can edit them directly within the Apps Script editor. You can even write macros from scratch in Apps Script, or take functions you’ve already written and turn them into macros.

Creating macros in Apps Script

You can take functions written in Apps Script and use them as macro functions. The easiest way to do this is by importing an existing function from the Google Sheets editor.

Alternatively, you can create macros within the Apps Script editor by following these steps:

1. In the Google Sheets UI, select Tools > Script editor to open the script bound to the sheet in the Apps Script editor.
2. Write the macro function. Macro functions should take no arguments and return no values.
3. Edit your script manifest to create the macro and link it to the macro function. Assign it a unique keyboard shortcut and name.
4. Save the script project. The macro is then available for use in the sheet.
5. Test the macro function in the sheet to verify that functions as intended.

Editing macros

You can edit macros attached to a sheet by doing the following:

1. In the Google Sheets UI, select Tools > Macros > Manage macros.
2. Find the macro you want to edit and select more_vert > Edit macro. This opens the Apps Script editor to the project file containing the macro function.
3. Edit the macro function to change the macro behavior.
4. Save the script project. The macro is then available for use in the sheet.
5. Test the macro function in the sheet to verify that functions as intended.

Importing functions as macros

If there is already a script bound to a sheet, you can import a function in the script as a new macro and then assign it a keyboard shortcut. You can do this by editing the manifest file and adding another element to the sheets.macros[] property.

Alternatively, follow these steps to import a function as a macro from the Sheets UI:

1. In the Google Sheets UI, select Tools > Macros > Import.
2. Select a function form the list presented and then click Add function.
3. Select clear to close the dialog.
4. Select Tools > Macros > Manage macros.
5. Locate the function you just imported in the list. Assign a unique keyboard shortcut to the macro. You can also change the macro name here; the name defaults to the name of the function.
6. Click Update to save the macro configuration.

Manifest structure for macros

The following manifest file example snippet shows the section of a manifest that defines Google Sheets macros. The sheets section of the manifest defines the name and keyboard shortcut assigned to the macro and the name of the macro function.

See the Manifest structure guide for more details on how Apps Script manifests are constructed. The Sheets and Macro sections describe the fields that define Sheets macros.

Best practices

When creating or managing macros in Apps Script, it is recommended that you adhere to the following guidelines.

1. Macros are more performant when they are light-weight. Where possible, limit the number of actions a macro takes.
2. Macros are best suited for rote operations that need to be repeated frequently with little or no configuation. For other operations, consider using a custom menu item instead.
3. Always remember that macro keyboard shortcuts must be unique, and a given sheet can only have ten macros with shortcuts at any one time. Any additional macros can only be executed from the Tools > Macros menu.
4. Macros that make changes to a single cell can be applied to a range of cells by first selecting the full range and then activating the macro. This means it is often unnecessary to create macros that duplicate the same operation across a predefined range of cells.

Things you can’t do

There are a few restrictions on what you can do with macros:

Use macros outside bound scripts

Macros are defined in scripts bound to specific Google Sheets. Macro definitions are ignored if defined in a standalone script or web app.

Define macros in Sheets add-ons

You cannot distribute macro definitions using a Sheets add-on. Any macro definitions in a Sheets add-on project are ignored by users of that add-on.

Distribute macros in script libraries

You cannot distribute macro definitions using Apps Script libraries.

Use macros outside of Google Sheets

Macros are only a feature in Google Sheets, and do not exist for Google Docs, Forms, or Slides.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

I’m having a bit of a struggle. I’m creating a spreadsheet which uses plus signs ( + ) regularly. I want a semi-permanent fix for + ‘s turning into addition formulae.

7 Answers 7

The easiest workaround is to enter an apostrophe ‘ as the first character, right before the + .

Another approach is to enter the contents as a string formula like =”+5 blah” .

An initial plus sign is very much needed for some types of data, e.g.- international phone numbers, so it is unfortunate that even setting the format to plain text does not help here.

In your Google Sheets select the area and go to FORMAT —–> NUMBER —–> PLAIN TEXT. Now the formulas will not work anymore!

Do a Find & Replace for = with ‘ and check “Also search within formulas.”

Just add a space before the + sign. Seemed to work for me

Depending on your application, the following solution could be useful:

Highlight the cell(s) that you would like to appear with a + . Go to the “123” Formatting, choose “More Formats,” then go to “Custom Number Format. “

Type “+”@ into the Custom Format box. This specifies to add a + before the entry no matter if it is a positive, negative, zero, or text entry.

Now, just type in your plain information, and it will appear with a + before it. The problem is that when dealing with formulas, the data will still be shown without the positive sign.

The way to do this is by adding an apostrophe (‘) before the + symbol, as written in Silver Ringvee’s answer. However, you can automate it using Google Apps Script.

Here is a simple Apps-Script script I created to solve the problem. Whenever you edit a cell, it searches through the sheet, and places apostrophe (‘) before the text, i.e. you can simply add +hello, without worrying about adding ‘ in the beginning. The script will do i automatically for you.

Usage-

Open the sheet, go to Tools->Script editor . In the editor that opens, paste the following code. Then add triggers to the script so that the script runs automatically whenever you edit a cell.

In the editor go to Resources -> Current project’s triggers . In the box that opens select Add a new trigger In column Run select main function, in Events select From spreadsheet , then select On edit for the last column.

In this tutorial, I’ll describe how to return values, errors and arrays from a custom function. This knowledge will hopefully be useful to you as you write your own custom functions in Google Sheets.

We’ll be writing a custom function called ONE_TO_N() in this tutorial. This function accepts a number N as a parameter and it outputs all of the numbers from 1 to N . This output is displayed on a single row starting with the cell where you entered the formula =ONE_TO_N( ) .

The screenshot below shows you the output returned by the function for N = 5.

We’ll start simple and we’ll progressively make changes to the function until we get the output we want. Along the way, we’ll learn more about returning values from a custom function.

Prerequisites

This tutorial assumes that you’re familiar with:

Returning a value from a custom function

We’ll start by simply returning the value N that is passed to the function. Here value is called an argument (or parameter) of the function. This variable value will contain the number N that users enter into the formula =ONE_TO_N(N) in the spreadsheet.

Open the script editor in Google Sheets and copy paste the above code into the editor. Once you do that, when you enter the formula =ONE_TO_N(5) in a cell, the output of the formula will be the value returned by your function. Since the function will return the value you provide to it, the value 5 will be displayed in the cell.

Returning an error from a custom function

When writing a custom function, it is a good practice to check if the input(s) provided by the user are valid. This is because your code may not work or produce incorrect output if the input is invalid. In this example, the function ONE_TO_N(value) expects the value to be a number. So, let’s explicitly check if the parameter value is a number or not using the typeof operator.

If it is a number, we’ll return the value just like before. However, if it is not a number, we will return an error. To return an error, use the keyword throw followed by the error message.

Replace the code in your script editor with the above code and then try entering the formula =ONE_TO_N(“five”) in your spreadsheet.

Here, “five” is a string and is not a number so an error will be returned.

Returning an array of values from a custom function

So far, we’ve written a custom function that returns a single value. However, we want it to return all of the numbers from one to N where N is the value you specify. That is, we want it to return an array of numbers from 1 to N.

We’ll create an array called output and use a for-loop to populate it with numbers from 1 to N. Then, we’ll return this array.

Replace the code in the script editor with the above code. Now, if you enter the formula =ONE_TO_N(5) in a cell, the custom function will be run and it will return the array [1,2,3,4,5] . This array will be displayed in your spreadsheet.

Notice that each value in the array is displayed in a separate row? This is because the spreadsheet is a two-dimensional grid consisting of rows and columns. This grid is represented in code as a two-dimensional array where the outer array contains rows and each row in turn is just an array of columns.

Example: A range in Google Sheets is represented in Apps Script as a two-dimensional array

Consider the following range in a Google Sheets spreadsheet. There are three rows and each row contains three columns.

The above range is represented in Apps Script as a two-dimensional array. The outer array contains three inner arrays, one for each row. Each of these inner arrays themselves contain three values, one for each column in that row.

Please also refer to the following tutorials for more information:

Returning a two-dimensional array from a custom function

Although, the code we’ve written so far displays the correct set of numbers, we’re not yet done. We want these numbers to be displayed in a single row. Therefore, we need to modify our code to return a two-dimensional array such that the outer array contains a single array (i.e., a single row) and this inner array contains the values 1 to 5.

Replace the code in your script editor with the above code.

Now when you enter the formula =ONE_TO_N(5) into a cell, instead of returning [1,2,3,4,5] , the custom function will return a two-dimensional array containing a single row with five columns:

We finally see the desired output in the spreadsheet!

Conclusion

In this tutorial I described how to return values, errors and arrays from a custom function. Hope you found this tutorial helpful and thanks for reading!

I’d appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!