+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: gspread - applying ValueRenderOption to a range of cells (/thread-36044.html)
gspread - applying ValueRenderOption to a range of cells - dwassner - Jan-12-2022
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