尝试使用 python/gspread 检查列'B2'是否包含 url，使用 '=image(B2)'更新列 C2 以在谷歌表格中显示图像

Question

我已经搜索了stackoverflow，并遇到了一堆变化，如if/else, range()， search()， enumerate, update_Cells, batch_update等，但我似乎无法实现我想要的结果。

尝试这个

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)

也试过这个:

# 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})')

但是似乎什么都不起作用!

Answer 1

问题1的答案:

当您想要将公式=IMAGE(B3),=IMAGE(B4)…放到列";C"中，如何将公式=ARRAYFORMULA(IMAGE(B3:B200))放到单元格";C3"中?

问题2的答案:

当你想把你的自定义函数=myFunction(B2),=myFunction(B3)…放到列";D"，如何把=BYROW(B3:B200,LAMBDA(X,IF(X<>"",IFERROR(myFunction(X),),)))的公式放到单元格";D3"?

或者，如果你可以修改你的脚本，如何修改你的脚本如下?

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];
});
}

• 通过此修改，可以将=myFunction("B3:B200")的自定义函数放入单元格"D3"这样，返回结果。