如何上传一个blob（图像）到一个谷歌表使用谷歌表api v 4

Question

我有一个blob数据。我想使用GoogleSheetAPIv4将其上传到GoogleSheet中的一个单元格。

我已经看了这里的文档。https://developers.google.com/sheets/api/guides/values

我在这里也看了这么多问题。使用Google Sheets API将图像插入Google Sheets单元格

result = service.spreadsheets().values().update(
spreadsheetId=spreadsheet_id, range=range_name,
valueInputOption=value_input_option, body=body).execute()

我没有看到任何描述为将blob作为图像插入的服务。请帮忙。

根据下面的建议，我们从这里实现了Webapp-使用GoogleSheetsAPI将图像插入GoogleSheets单元格

这就是我们从python代码调用web应用程序的方式

        dropoff_signature = "ZGF0YT <clip > WVhSaA=="
        web_app_url     = "https://script.google.com/macros/s/A < clip > y/exec"        
        image_data  = "data:image/png;base64," + dropoff_signature
        data_to_post = {            
            'spreadsheetid' : spreadsheet_Id, 
            'sheetname' : 'Sheet1',     
            'imageurl'  : image_data,              
            'column'    : 5, 
            'row'       : 5             
            }
        encoded_data = urllib.urlencode(data_to_post)
        # Send encoded data to application-2
        url_result = urlfetch.fetch(web_app_url, encoded_data, method='POST')           

我们在Webapp中看到以下错误。

result : 200 content : {"status":"error","defaultMessage":"Error retrieving image from URL or bad URL: data:image/png;base64, <clip> ","name":"Exception","fileName":"Code (Insert image into spreadsheet)","lineNumber":42,"stack":"\tat Code (Insert image into spreadsheet):42 (doPost)\n"}}

你能帮忙吗？

我做了这个改变。仍然收到错误的URL错误。

dropoff_signature = "ZGF0YTpp<clip>WVhSaA=="
        web_app_url     = "https://script.google.com/macros/s/A<clip>y/exec"        
        image_data  = "data:image/png;base64," + dropoff_signature
        data_to_post = {            
            'spreadsheetid' : spreadsheet_Id, 
            'sheetname' : 'Sheet1',     
            'imageurl'  : image_data,              
            'column'    : 5, 
            'row'       : 5             
            }
        # encoded_data = urllib.urlencode(data_to_post)
        # Send encoded data to application-2
        # url_result = urlfetch.fetch(web_app_url, encoded_data, method='POST')         
        url_result = urlfetch.fetch(url=web_app_url, payload=json.dumps(data_to_post), method='POST', headers={'Content-type': 'application/json'})



result : 200 content : {"status":"error","defaultMessage":"Error retrieving 
image from URL or bad URL: 
data:image/png;base64,Z<clip>A==","error": 
{"message":"Error retrieving image from URL or bad URL: data:image/png;base64,Z<clip>A==","name":"Exception","fileName":"Code (Insert image into spreadsheet)","lineNumber":42,"stack":"\tat Code (Insert image into spreadsheet):42 (doPost)\n"}}

这是我们正在使用的Webapp。

function doGet(e) {
  return ContentService.createTextOutput("Authorization: Bearer " + 
  ScriptApp.getOAuthToken())
}

//
// Example curl command to insert an image:
// 
// curl -L -d '{ "spreadsheetid": "1xNDWJXOekpBBV2hPseQwCRR8Qs4LcLOcSLDadVqDA0E","sheetname": "Sheet1", "imageurl": "https://www.google.com/images/srpr/logo3w.png", "column": 1, "row": 1 }' \
// -H "Authorization: Bearer <INSERT TOKEN RETURNED FROM GET HERE>" \
// -H 'Content-Type: application/json' \
// https://script.google.com/a/tillerhq.com/macros/s/AKfycbzjFgIrgCfZTvOHImuX54G90VuAgmyfz2cmaKjrsNFrTzcLpNk0/exec
//

var REQUIRED_PARAMS = [
  'spreadsheetid', // example: "1xNDWJXOekpBBV2hPseQwCRR8Qs4LcLOcSLDadVqDA0E"
  'sheetname',     // Case-sensitive; example: "Sheet1"
  'imageurl',      // Can be an url such as "https://www.google.com/images/srpr/logo3w.png"
                   // or alternately "data:image/png;base64,iVBOR...<snip>...gg=="
  'column', // 1-based (i.e. top left corner is column 1)
  'row'     // 1-based (i.e. top left corner is row 1)
];

function doPost(e) {

  var result = {
    status: "ok",
    defaultMessage: "Image inserted."
  }

  try {
    var params = (e.postData && e.postData.type == "application/x-www-form-urlencoded") ? e.parameter
    : (e.postData && e.postData.type == "application/json") ? JSON.parse(e.postData.contents)
    : undefined;


    if (!params) throw new Error('Unsupported content-type, must be either application/x-www-form-urlencoded or application/json.');

    REQUIRED_PARAMS.forEach(function(requiredParam) {
      if (!params[requiredParam]) throw new Error('Missing required parameter ' + requiredParam);
    });

    SpreadsheetApp.openById(params.spreadsheetid).getSheetByName(params.sheetname).insertImage(params.imageurl, params.column, params.row);  

  } catch(e) {

    console.error(e); 

    result.status = "error";
    result.error = e;
    result.defaultMessage = e.message;

  }  

  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON)  
}

Answer 1

解决方案一：

在Python应用程序中，您可以使用以下代码使用Sheets API[1]使用图像公式设置图像。您需要放置电子表格ID并更改图像的显示范围。

spreadsheet_id = '[SPREADSHEET-ID]'
range_name = 'D13'

service = build('sheets', 'v4', credentials=creds)

values = [
    [
       '=IMAGE("https://google.com","google")'
    ]
]
body = {
    'values': values
}
result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption='USER_ENTERED', body=body).execute()

解决方案2：

相反，如果您想使用应用程序脚本中的insertImage函数[2]，则可以将网格上的图像插入图纸，而不是链接到单元格的图像。您可以使用doPost（）函数部署Web应用程序[3]，并使用服务帐户凭据从Python应用程序调用Web应用程序。此外，您还需要部署Web应用程序以“用户访问Web应用程序”的身份执行，这样您从Web应用程序执行的所有请求都将使用服务帐户凭据进行。

Python脚本：

from google.oauth2 import service_account
import requests
import json
import google.auth.transport.requests

SCOPES = ['https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = 'service_account.json'

credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)

delegated_credentials = credentials.with_subject('[USER-EMAIL-TO-IMPERSONATE]')
delegated_credentials.refresh(google.auth.transport.requests.Request())
token = delegated_credentials.token

headers = {'content-type': 'application/json', 'Authorization': 'Bearer ' + token}
url = '[WEB-APP-URL]'
data = {"file": '[blob]'}
response = requests.post(url, data=json.dumps(data), headers=headers)

Web应用程序脚本：

function doPost(e) { 
  var ss = SpreadsheetApp.openById('[SPREADSHEET-ID]');
  var sheet = ss.getSheets()[0];
  var blob = DriveApp.getFileById("[IMAGE-ID]").getBlob();
  sheet.insertImage(blob, 4, 14);

  return ContentService.createTextOutput("Good");
}

我用从Web App中的Drive获得的图像测试了我的代码。您可以跳过这一部分，直接从数据负载中的Python应用程序发送Blob。

要使用服务帐户，请记住向API授予对所有所需作用域的访问权限，您需要转到admin。谷歌。com-

[1] https://developers.google.com/sheets/api/guides/values

[2] https://developers.google.com/apps-script/reference/spreadsheet/sheet#insertImage（BlobSource、Integer、Integer）

[3] https://developers.google.com/apps-script/guides/web