Skip to main content
added 90 characters in body
Source Link
Graipher
  • 41.1k
  • 7
  • 69
  • 133

The update_cells call can update multiple cells at once. So you should calculate all new values locally and push the changes in one update. This should use only one token, instead of one per cell (untested code):

def update_sheet(sheet, table, start='A', end='C'):
    to_update = []
    for i, row in enumerate(table):
        cells = sheet.range(f'{start}{i+4}:{end}{i+4}')
        for cell, value in zip(cells, row):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

I also changed the names and indentation to follow Python's official style-guide, PEP8, and used an f-string for easier string formatting.

This still has the problem that it needs to get each row from the sheet, each of which does an API call. Instead you can get each column as a range and update one column at a time, which saves calls if your table has less columns than rows. For this we need to transpose the table first, though:

def update_sheet(sheet, table, columns="ABC", header=4):
    to_update = []
    table = list(zip(*table))    # transpose the table
    for col_name, col in zip(columns, table):   # iterating over columns now
        r = f"{col_name}{header}:{col_name}{len(col)+header}"  # changed range
        print(r)  # for debugging
        cells = sheet.range(r)
        for cell, value in zip(cells, col):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

With this it is no problem to use e.g. this table of size 2500 x 3:

import numpy as np

...
table = list(map(list, np.arange(2500*3).reshape(-1, 3).astype(str)))
update_sheet(sheet, table)

The update_cells call can update multiple cells at once. So you should calculate all new values locally and push the changes in one update. This should use only one token, instead of one per cell (untested code):

def update_sheet(sheet, table, start='A', end='C'):
    to_update = []
    for i, row in enumerate(table):
        cells = sheet.range(f'{start}{i+4}:{end}{i+4}')
        for cell, value in zip(cells, row):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

I also changed the names and indentation to follow Python's official style-guide, PEP8, and used an f-string for easier string formatting.

This still has the problem that it needs to get each row from the sheet, each of which does an API call. Instead you can get each column as a range and update one column at a time. For this we need to transpose the table first:

def update_sheet(sheet, table, columns="ABC", header=4):
    to_update = []
    table = list(zip(*table))    # transpose the table
    for col_name, col in zip(columns, table):   # iterating over columns now
        r = f"{col_name}{header}:{col_name}{len(col)+header}"  # changed range
        print(r)  # for debugging
        cells = sheet.range(r)
        for cell, value in zip(cells, col):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

With this it is no problem to use e.g. this table of size 2500 x 3:

import numpy as np

...
table = list(map(list, np.arange(2500*3).reshape(-1, 3).astype(str)))
update_sheet(sheet, table)

The update_cells call can update multiple cells at once. So you should calculate all new values locally and push the changes in one update. This should use only one token, instead of one per cell (untested code):

def update_sheet(sheet, table, start='A', end='C'):
    to_update = []
    for i, row in enumerate(table):
        cells = sheet.range(f'{start}{i+4}:{end}{i+4}')
        for cell, value in zip(cells, row):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

I also changed the names and indentation to follow Python's official style-guide, PEP8, and used an f-string for easier string formatting.

This still has the problem that it needs to get each row from the sheet, each of which does an API call. Instead you can get each column as a range and update one column at a time, which saves calls if your table has less columns than rows. For this we need to transpose the table first, though:

def update_sheet(sheet, table, columns="ABC", header=4):
    to_update = []
    table = list(zip(*table))    # transpose the table
    for col_name, col in zip(columns, table):   # iterating over columns now
        r = f"{col_name}{header}:{col_name}{len(col)+header}"  # changed range
        print(r)  # for debugging
        cells = sheet.range(r)
        for cell, value in zip(cells, col):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

With this it is no problem to use e.g. this table of size 2500 x 3:

import numpy as np

...
table = list(map(list, np.arange(2500*3).reshape(-1, 3).astype(str)))
update_sheet(sheet, table)
added 90 characters in body
Source Link
Graipher
  • 41.1k
  • 7
  • 69
  • 133

The update_cells call can update multiple cells at once. So you should calculate all new values locally and push the changes in one update. This should use only one token, instead of one per cell (untested code):

def update_sheet(sheet, table, start='A', end='C'):
    to_update = []
    for i, row in enumerate(table):
        cells = sheet.range(f'{start}{i+4}:{end}{i+4}')
        for cell, value in zip(cells, row):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

I also changed the names and indentation to follow Python's official style-guide, PEP8, and used an f-string for easier string formatting.

This still has the problem that it needs to get each row from the sheet, each of which does an API call. Instead you can get each column as a range and update one column at a time. For this we need to transpose the table first:

def update_sheet(sheet, table, columns="ABC", header=4):
    to_update = []
    table = list(zip(*table))    # transpose the table
    for col_name, col in zip(columns, table):   # iterating over columns now
        r = f"{col_name}{header}:{col_name}{len(col)+header}"  # changed range
        print(r)  # for debugging
        cells = sheet.range(r)
        for cell, value in zip(cells, col):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

With this it is no problem to use e.g. this table of size 2500 x 3:

import numpy as np

...
table = list(map(list, np.arange(2500*3).reshape(-1, 3).astype(str)))
update_sheet(sheet, table)

The update_cells call can update multiple cells at once. So you should calculate all new values locally and push the changes in one update. This should use only one token, instead of one per cell (untested code):

def update_sheet(sheet, table, start='A', end='C'):
    to_update = []
    for i, row in enumerate(table):
        cells = sheet.range(f'{start}{i+4}:{end}{i+4}')
        for cell, value in zip(cells, row):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

I also changed the names and indentation to follow Python's official style-guide, PEP8, and used an f-string for easier string formatting.

This still has the problem that it needs to get each row from the sheet, each of which does an API call. Instead you can get each column as a range and update one column at a time. For this we need to transpose the table first:

def update_sheet(sheet, table, columns="ABC", header=4):
    to_update = []
    table = list(zip(*table))
    for col_name, col in zip(columns, table):
        r = f"{col_name}{header}:{col_name}{len(col)+header}"
        print(r)
        cells = sheet.range(r)
        for cell, value in zip(cells, col):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

With this it is no problem to use e.g. this table of size 2500 x 3:

import numpy as np

...
table = list(map(list, np.arange(2500*3).reshape(-1, 3).astype(str)))
update_sheet(sheet, table)

The update_cells call can update multiple cells at once. So you should calculate all new values locally and push the changes in one update. This should use only one token, instead of one per cell (untested code):

def update_sheet(sheet, table, start='A', end='C'):
    to_update = []
    for i, row in enumerate(table):
        cells = sheet.range(f'{start}{i+4}:{end}{i+4}')
        for cell, value in zip(cells, row):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

I also changed the names and indentation to follow Python's official style-guide, PEP8, and used an f-string for easier string formatting.

This still has the problem that it needs to get each row from the sheet, each of which does an API call. Instead you can get each column as a range and update one column at a time. For this we need to transpose the table first:

def update_sheet(sheet, table, columns="ABC", header=4):
    to_update = []
    table = list(zip(*table))    # transpose the table
    for col_name, col in zip(columns, table):   # iterating over columns now
        r = f"{col_name}{header}:{col_name}{len(col)+header}"  # changed range
        print(r)  # for debugging
        cells = sheet.range(r)
        for cell, value in zip(cells, col):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

With this it is no problem to use e.g. this table of size 2500 x 3:

import numpy as np

...
table = list(map(list, np.arange(2500*3).reshape(-1, 3).astype(str)))
update_sheet(sheet, table)
added 912 characters in body
Source Link
Graipher
  • 41.1k
  • 7
  • 69
  • 133

The update_cells call can update multiple cells at once. So you should calculate all new values locally and push the changes in one update. This should use only one token, instead of one per cell (untested code):

def update_sheet(sheet, table, start='A', end='C'):
    to_update = []
    for i, row in enumerate(table):
        cells = sheet.range(f'{start}{i+4}:{end}{i+4}')
        for cell, value in zip(cells, row):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

I also changed the names and indentation to follow Python's official style-guide, PEP8, and used an f-string for easier string formatting.

This still has the problem that it needs to get each row from the sheet, each of which does an API call. Instead you can get each column as a range and update one column at a time. For this we need to transpose the table first:

def update_sheet(sheet, table, columns="ABC", header=4):
    to_update = []
    table = list(zip(*table))
    for col_name, col in zip(columns, table):
        r = f"{col_name}{header}:{col_name}{len(col)+header}"
        print(r)
        cells = sheet.range(r)
        for cell, value in zip(cells, col):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

With this it is no problem to use e.g. this table of size 2500 x 3:

import numpy as np

...
table = list(map(list, np.arange(2500*3).reshape(-1, 3).astype(str)))
update_sheet(sheet, table)

The update_cells call can update multiple cells at once. So you should calculate all new values locally and push the changes in one update. This should use only one token, instead of one per cell (untested code):

def update_sheet(sheet, table, start='A', end='C'):
    to_update = []
    for i, row in enumerate(table):
        cells = sheet.range(f'{start}{i+4}:{end}{i+4}')
        for cell, value in zip(cells, row):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

I also changed the names and indentation to follow Python's official style-guide, PEP8, and used an f-string for easier string formatting.

The update_cells call can update multiple cells at once. So you should calculate all new values locally and push the changes in one update. This should use only one token, instead of one per cell (untested code):

def update_sheet(sheet, table, start='A', end='C'):
    to_update = []
    for i, row in enumerate(table):
        cells = sheet.range(f'{start}{i+4}:{end}{i+4}')
        for cell, value in zip(cells, row):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

I also changed the names and indentation to follow Python's official style-guide, PEP8, and used an f-string for easier string formatting.

This still has the problem that it needs to get each row from the sheet, each of which does an API call. Instead you can get each column as a range and update one column at a time. For this we need to transpose the table first:

def update_sheet(sheet, table, columns="ABC", header=4):
    to_update = []
    table = list(zip(*table))
    for col_name, col in zip(columns, table):
        r = f"{col_name}{header}:{col_name}{len(col)+header}"
        print(r)
        cells = sheet.range(r)
        for cell, value in zip(cells, col):
            cell.value = value
        to_update.extend(cells)
    sheet.update_cells(to_update)

With this it is no problem to use e.g. this table of size 2500 x 3:

import numpy as np

...
table = list(map(list, np.arange(2500*3).reshape(-1, 3).astype(str)))
update_sheet(sheet, table)
Source Link
Graipher
  • 41.1k
  • 7
  • 69
  • 133
Loading