1

I'm not sure if this is possible. I have tons of spreadsheet, and the formulas need to be updated. How do I copy a formula from one cell or a group of cells to another? I've used gspread and it seems it can only do values. I need python to basically paste formulas on hundreds of sheets for me, without me opening each individually and copy and pasting the formulas.

Does anybody have a generic solution for copying and pasting formulas? This is pretty important, you would think someone can do it.

jason
  • 3,811
  • 18
  • 92
  • 147

1 Answers1

7

Update 19 July 2018:

Here's how you do it:

# Get the formula value from the souce cell:
formula = wks.acell('A2', value_render_option='FORMULA').value

# Update the target cell with formula:
wks.update_acell('B3', formula)

(this works since gspread 3.0.0 which uses Sheets API v4.)

Original answer below:

To access a formula value, you need to use the input_value attribute of a cell object.

Here's an example. (I'm skipping the initialization step. Let's assume you've already opened a spreadsheet and wks is referring to you worksheet object.)

# To copy a formula from a single cell (say, A2) to another cell (B3)
# use the input_value property
formula = wks.acell('A2').input_value

# then paste the value to a new cell
wks.update_acell('B3', formula)

For a group of cells, you'd want to use a wks.range() method to get cell objects. Then you can get formula values via input_value as in the code above. See the example on the GitHub.

Burnash
  • 3,181
  • 2
  • 31
  • 35
  • What version of API does `gspread` use? I see that `pygsheets` is using API v4. What is `gspread` using and what do you plan to do? What's the roadmap? – jason Feb 02 '17 at 17:16
  • Presently, it's v3, but I'm working on upgrading the library to v4. It's already using v4 for new features (e.g. sharing a spreadsheet.) – Burnash Feb 02 '17 at 21:48
  • 1
    doesn't work. value of `formula` becomes `None` even when there is a formula at `A2`. – manas Jul 18 '18 at 21:12
  • 1
    use `wks.acell('A2', value_render_option='FORMULA').value` instead. `input_value` doesn't work anymore. – manas Jul 19 '18 at 08:49
  • 1
    This solution was also helpful for me in writing formulas to google sheet cells: https://stackoverflow.com/a/50648449/764307 – Jonathan B. May 18 '19 at 23:01