1

I am trying to generate a large number of formulas programmatically.

I can easily generate a formula text doing something like ="=1+1" (in reality it will be a bit more complex). This will output the string =1+1 in the cell.

I can then copy-paste_as_values the content of this cell in another cell. This new cell will now contain =1+1. This should be a formula and the cell should display 2. However, for some reason, the formula is not evaluated and =1+1 is displayed. Note that there are no quotation marks in the cell content. In theory there should be no way for a cell containing =1+1 to not display 2. The weird part is that when I select the cell, the cell code is linted as it was a proper formula, still the result is not shown...

The only way I found is to delete the = sign, press enter, go back to the same cell, add the = again, and press enter again.

Is there a way to "refresh" Google Sheet to let him interpret the formula properly?

NOTE: I know this is not the "proper" way of doing this. I could simply keep the variable part of the formula in an array and reference it. However, due to the way the spreadsheet is organised, this would become cumbersome and ugly.

EDIT: Working example

I'm adding a working example a bit closer to what I need

https://docs.google.com/spreadsheets/d/1ME1V81BnYpOHqe6xc5ZRF2p8RYDJOkvARxv-2iDXkcQ/edit?usp=sharing

I have a tab containing some cells with a value in them. I want to link each of these to the corresponding cell in another tab. Each cell will have a different link, so I found 3 options (that you can see in the example attached):

  1. Add each link manually (impossible as I have more than 400 hundreds!
  2. Use the formula HYPERLINK "hard-coding" the value to search and the label in each formula. Not very different than the solution above as I'd need to have 400 different formulas and arrange them manually
  3. Keep a cell with the name/label and generate the link in another cell using the HYPERLINK formula. Works, but I need two rows/cells where I had one. Also, one becomes completely redundant. Ugly.
  4. Generates the customised formulas as per option 1 programmatically. I can then copy-paste all the formulas generated in the correct place, overwriting the previous value. The result is the same as option 1, but I do this automatically instead than 1 by 1. Here is where I find that if you copy a formula generated programmatically, this is not interpreted as a formula
3
  • 1
    @Blindspots I added a working example. I hope this clarifies my needs. The MRE imho is still the =1+1 I provided, but this gives you a better understanding of why I'm trying to do this.
    – Luca
    Commented Mar 6 at 16:55
  • 1
    @Blindspots PS: I didn't know what an XY problem was. I agree with you this could be the case. Still I'm not sure there is another "Y" to this "X". The only other option that comes to my mind is to use a script. But I've never done that in Google Sheets and this looks an even more complicated "Y" than what I'm trying to do. Happy to be proven wrong though
    – Luca
    Commented Mar 6 at 17:01
  • Related: stackoverflow.com/questions/16303680/…
    – TheMaster
    Commented Mar 7 at 8:56

2 Answers 2

2

Overview

You paste a text string, not a formula, into another cell and Sheets sets the cell's number format to match the value.

You need to trigger a re-evaluation of the cell contents by Sheets before it will be treated as a formula.

Two Different Approaches

Both of the following approaches are convenient and may lend themselves well to recording a macro if warranted:

1. Change a value in the cell(s)

  1. Paste your text value(s)
  2. With the value(s) selected, open the Find and replace dialog,
    Ctrl+H   [ or ]   Edit > Find and replace
    • Check Also search within formulas
    • Find the equals symbol =
    • Replace with the equals symbol =
  3. Click Replace all

    Find and Replace dialog box

2. Change the number format

  1. Paste your text value(s)
  2. With the value(s) selected, change the number format to Number
    Ctrl+Shift+1   [ or ]   Format > Number > Number
1
  • 2
    Both solutions are extremely quick and both do exactly what I needed, forcing Google Sheet to re-evaluate the string as a formula.
    – Luca
    Commented Mar 7 at 8:47
2

From the question

Is there a way to "refresh" Google Sheet to let him interpret the formula properly?

You might try to use find and replace to remove the parts of your "formula" that you don't want. Using regular expressions might be handy in cases like this.

Related


The "Google way" for end-users to automate Google Sheets is to use Google Apps Script. It can be utilized to automate the creation of Google Sheets formulas.

To learn about this, start by reading https://developers.google.com/apps-script/guides/sheets.

More specifically you will have to use JavaScript as programming language and the Class SpreadsheetApp from Google Apps Script to read/write things from a Google Sheets spreadsheet.

  • Class Spreadsheet
  • Class Sheet
  • Class Range

Class Range methods: setFormula and setFormulas

The documentation has several examples that you might find helpful.

You might try using the Google Sheets macro recorder. It quickly creates JavaScript functions with Google Apps Script classes and methods, but it might be tricky for people new to programming.

A workaround might be using QUERY built-in function as it supports a few arithmetic operations and include some functions like SUM, AVG (average) among others.

3
  • Yup. This was exactly what I was trying to avoid. I'm a good programmer with other languages (e.g. Python), but as I'm not familiar with JavaScript, learning a new language just to automatise this looks quite an overkill, but I understand my solution could be seen as a workaround. I can try to give it a look, but this looks almost more effort than doing this by hand
    – Luca
    Commented Mar 6 at 17:07
  • 1
    @Luca If you see learning Google Apps Script / JavaScript as something that will be helpful only once in a lifetime, then yes, it might be more work for someone with new programming and JavaScript, if the number of formulas to write is not really big. Commented Mar 6 at 17:09
  • 1
    @Luca I updated my answer. Commented Mar 6 at 17:16

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.