Jan-12-2022, 03:05 PM
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
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