gspread - applying ValueRenderOption to a range of cells

[dwassner]

I am able to successfully update the cell values of the target Google Sheet, and am successfully changing a specific range of cells to become an active formula.

What I cannot figure out is how to apply

value_render_option='FORMATTED_VALUE'

to the range of cells (J2:J16) rather than updating them one at a time. Is this possible? Thank you.

import gspread

dflist = [['Customer Search Term', 'Search Term Impression Share', 'Match Type', 'Currency', 'Clicks', 'Impressions', 'Click-Thru Rate (CTR)', 'Spend', 'Cost Per Click (CPC)', 'formula'], ['mini cinder blocks', '100.00%', 'TARGETING_EXPRESSION', 'USD', 1, 1, '100.00%', '$0.12 ', '$0.12 ', '=A2'], ['mini cinder blocks', '100.00%', 'BROAD', 'USD', 14, 5322, '0.26%', '$1.38 ', '$0.10 ', '=A3'], ['mini cinder blocks', '100.00%', 'BROAD', 'USD', 41, 9216, '0.44%', '$3.98 ', '$0.10 ', '=A4'], ['mini cinder blocks', '99.52%', 'EXACT', 'USD', 36, 7839, '0.46%', '$25.27 ', '$0.70 ', '=A5'], ['mini cinder blocks', '99.52%', 'EXACT', 'USD', 7, 117, '5.98%', '$0.48 ', '$0.07 ', '=A6'], ['mini cinder blocks', '99.52%', 'TARGETING_EXPRESSION', 'USD', 1, 19, '5.26%', '$1.34 ', '$1.34 ', '=A7'], ['mini cinder blocks', '99.52%', 'BROAD', 'USD', 4, 47, '8.51%', '$0.42 ', '$0.11 ', '=A8'], ['mini cinder blocks', '79.82%', 'BROAD', 'USD', 3, 244, '1.23%', '$0.30 ', '$0.10 ', '=A9'], ['mini cinder blocks', '79.82%', 'BROAD', 'USD', 10, 1022, '0.98%', '$0.91 ', '$0.09 ', '=A10'], ['mini cinder blocks', '97.60%', 'BROAD', 'USD', 7, 780, '0.90%', '$0.68 ', '$0.10 ', '=A11'], ['mini cinder blocks', '97.60%', 'TARGETING_EXPRESSION', 'USD', 8, 29, '27.59%', '$1.29 ', '$0.16 ', '=A12'], ['mini cinder blocks', '97.60%', 'TARGETING_EXPRESSION', 'USD', 4, 19, '21.05%', '$0.43 ', '$0.11 ', '=A13'], ['mini cinder blocks', '97.60%', 'BROAD', 'USD', 3, 182, '1.65%', '$0.30 ', '$0.10 ', '=A14'], ['mini cinder blocks', '97.60%', 'PHRASE', 'USD', 1, 5, '20.00%', '$0.58 ', '$0.58 ', '=A15'], ['mini cinder blocks', '100.00%', 'BROAD', 'USD', 8, 898, '0.89%', '$0.78 ', '$0.10 ', '=A16']]

sa = gspread.service_account_from_dict(info=info)
sh = sa.open_by_key(sheetkey)
newwks = sh.add_worksheet(
    title="Test Worksheet",
    rows="25",
    cols="25"
)

newwks.update('A1:J16', dflist)

startingrow = 2
currentrow = 1
rowcount = 16
formattingcol = 'J'
while currentrow <= rowcount:
    formula = newwks.acell(
        formattingcol + str(currentrow + startingrow - 1), value_render_option='FORMATTED_VALUE').value
    newwks.update_acell(
        formattingcol + str(currentrow + startingrow - 1),
        formula)
    currentrow += 1