Trying to use python/gspread to check if column 'B2' contains url, update column C2 with &-CodePudding

I've searched stackoverflow and came across a bunch of variations like if/else, range(), search(), enumerate, update_Cells, batch_update, etc but I can't seem to achieve my desired result.

Tried this

import gspread

NAMES=["https"] 
gc = gspread.service_account()
spreadsheet = gc.open("SpreadsheetName") 

sheet = spreadsheet.worksheet("sheet1")

data = sheet.get_all_values()
values = ["Yes" if cell.value in NAMES else "No" for b in data]
cells = sheet.range("C1:C%d" % len(values))
for i, b in enumerate(cells):
    b.value = values[i]
sheet.update_cells(cells)

Also tried this:


# Import the necessary libraries
from gspread import Client

# Authenticate with Google Sheets and open the desired spreadsheet
client = Client.from_service_account_file('/path/to/service/account/credentials.json')
spreadsheet = client.open('My Spreadsheet')

# Select the worksheet that contains the desired cells
worksheet = spreadsheet.worksheet('Sheet1')

# Define the regular expression pattern for a URL
url_pattern = r'(https?://)?([\w-]{2,}\.) [\w-]{2,4}(/[\w-./?%&=_]*)?'

# Search for cells containing a URL in the desired range
results = worksheet.findall(url_pattern, 'B2:B3')

# If cells containing a URL are found, write the =image() formula in the corresponding cells
if results:
    for cell in results:
        # Get the row and column indices of the matching cell
        row = cell.row
        col = cell.col

        # Write the =image() formula in the corresponding cell in the other column
        worksheet.update_cell(row, col   1, f'=image({worksheet.cell(row, col).address})')

But nothing seems to work!

CodePudding user response：

Answer to question 1:

When you want to put the formula of =IMAGE(B3), =IMAGE(B4) ,,, to the column "C", how about putting a formula of =ARRAYFORMULA(IMAGE(B3:B200)) to the cell "C3"?

Answer to question 2:

When you want to put your custom function of =myFunction(B2), =myFunction(B3),,, to the column "D", how about putting a formula of =BYROW(B3:B200,LAMBDA(X,IF(X<>"",IFERROR(myFunction(X),),))) to a cell "D3"?

Or, if you can modify your script, how about modifying your script as follows?

function myFunction(urls) {
  const reqs = urls && Array.isArray(urls) ? urls.map(([url]) => (url ? { url, muteHttpExceptions: true } : null)) : [{ url: urls, muteHttpExceptions: true }];
  return reqs.map(e => {
    if (e) {
      const res = UrlFetchApp.fetchAll([e])[0];
      if (res.getResponseCode() == 200) {
        const { width, height } = ImgApp.getSize(res.getBlob());
        var percDiff = (width / height) * 100;
        return [percDiff < 85 ? "Story" : "Post"];
      }
    }
    return [null];
  });
}

By this modification, you can put a custom function of =myFunction("B3:B200") to a cell "D3". By this, the results are returned.