Python传大列表至Google Apps Script Web App更新表格遇400错误

我尝试用GET和POST两种方法，将Python生成的大列表（源自Pandas DataFrame）及一个URL作为参数，请求Google Apps Script Web App更新Google Sheet。数据已成功写入表格，但两种方法均返回400 Bad Request错误。

测试的请求方式 #

GET请求 #

Python代码：

web_app_response = requests.get(
    webAppsUrl, headers=headers, params=params, timeout=360
)

Google Apps Script对应函数：

function doGet(e) {}

POST请求 #

Python代码：

web_app_response = requests.post(
    webAppsUrl, headers=headers, params=params, timeout=360
)

Google Apps Script对应函数：

function doPost(e) {}

Python完整请求代码 #

import requests
import json

UAGENT = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36'
headers = {
    'User-Agent': UAGENT
    }

df_list = [['W. Yarbrough', '/players/william-paul-yarbrough-story/224324/', 'J. McCarthy', '/players/john--mccarthy/298849/', 'R. Priso-Mbongue', '/players/ralph-priso-mbongue/497224/', 'J. Murillo', '/players/jesus-david-murillo-largacha/238360/', 'Colorado Rapids', '/teams/united-states/colorado-rapids/2278/', 'https://secure.cache.images.core.optasports.com/soccer/teams/150x150/2278.png', 'D', 'https://a/b/2023/03/26/c/d/e/f/123456/', 'W', 'https://a/b/2023/03/26/c/d/e/f/123456/', 'USA', 'https://a/b/2023/03/26/c/d/e/f/123456/', 3.5, '', 'MLS', 'name large-link Link', 'shirtnumber sortdefaultasc', 'photo', 'text name sortdefaultasc', 'flag', 'number age', 'text position sortasc', 'number statistic game-minutes', 'number statistic appearances', 'number statistic lineups', 'number statistic subs-in', 'number statistic subs-out', 'number statistic subs-on-bench', 'number statistic goals', 'number statistic assists', 'number statistic yellow-cards', 'number statistic 2nd-yellow-cards', 'number statistic red-cards', '', 'MLS', 'name large-link Link', 'shirtnumber sortdefaultasc', 'photo', 'text name sortdefaultasc', 'flag', 'number age', 'text position sortasc', 'number statistic game-minutes', 'number statistic appearances', 'number statistic lineups', 'number statistic subs-in', 'number statistic subs-out', 'number statistic subs-on-bench', 'number statistic goals', 'number statistic assists', 'number statistic yellow-cards', 'number statistic 2nd-yellow-cards', 'number statistic red-cards', '', 'lat=39.80567609&lon=-104.891806841', "Dick's Sporting Goods Park", '', 'League', 'MLS - 2023', '', 'League', 'MLS - 2023', '', '/players/brad-stuver/296250/', '/players/william-paul-yarbrough-story/224324/', '/players/jt-marcinkowski/474554/', '', '/players/john--mccarthy/298849/', '/players/stefan-frei/73572/', '/players/john--mccarthy/298849/'], ['D. Wilson', '/players/daniel-wilson/88038/', 'G. Chiellini', '/players/giorgio-chiellini/17684/', 'D. Yapi', '/players/darren-yapi/591906/', 'D. Maldonado', '/players/denil-omar-maldonado-munguia/418816/', 'Los Angeles', '/teams/united-states/los-angeles-fc/41871/', 'https://secure.cache.images.core.optasports.com/soccer/teams/150x150/41871.png', 'L', 'https://a/b/2023/03/26/c/d/e/f/123456/', 'D', 'https://a/b/2023/03/26/c/d/e/f/123456/', 'MLS', '', 2.18, '', '2023', '/players/andreas-maxso/277505/', 5, 'https://secure.cache.images.core.optasports.com/soccer/players/18x18/277505.png', 'A. Maxsø', '', 29, 'D', 450, 5, 5, 0, 0, 0, 0, 0, 1, 0, 0, '', '2023', '/players/john--mccarthy/298849/', 77.0, 'https://secure.cache.images.core.optasports.com/soccer/players/18x18/298849.png', 'J. McCarthy', '', 30, 'G', 360, 4, 4, 0, 0, 0, 0, 0, 0, 0, 0, '', 'Location', 'Commerce City', '', 'Rank', '14', '', 'Rank', '3', '', '/players/an-kolmani/466622/', '/players/daniel-wilson/88038/', '/players/jonathan-mensah/62379/', '', '/players/giorgio-chiellini/17684/', '/players/yeimar-pastor-gomez-andrade/308766/', '/players/giorgio-chiellini/17684/'], ['A. Maxsø', '/players/andreas-maxso/277505/', 'R. Hollingshead', '/players/ryan-michael-hollingshead/332676/', 'K. Cabral', '/players/kevin-cabral/460281/', 'N. Ordaz', '/players/nathan-ordaz/637317/', '', '', '', 'L', 'https://a/b/2023/03/26/c/d/e/f/123456/', 'L', 'https://a/b/2023/03/26/c/d/e/f/123456/', '', '', 3.9, '', '', '/players/william-paul-yarbrough-story/224324/', 22, 'https://secure.cache.images.core.optasports.com/soccer/players/18x18/224324.png', 'W. Yarbrough', '', 34, 'G', 450, 5, 5, 0, 0, 0, 0, 0, 0, 0, 0, '', '', '/players/kellyn-kai-perry-acosta/172800/', 23.0, 'https://secure.cache.images.core.optasports.com/soccer/players/18x18/172800.png', 'K. Acosta', '', 27, 'M', 352, 4, 4, 0, 1, 0, 0, 0, 1, 0, 0, '', 'Country', 'US', '', 'Matches played', '5', '', 'Matches played', '4', '', '/players/nick-lima/474083/', '/players/andreas-maxso/277505/', '/players/miguel-trauco/176390/', '', '/players/jesus-david-murillo-largacha/238360/', '/players/nouhou-tolo/442347/', '/players/sergi-palencia-hurtado/314218/'], ['K. Rosenberry', '/players/keegan-rosenberry/298858/', 'A. Long', '/players/aaron-long/335436/', 'M. Edwards', '/players/michael-edwards/489485/', 'Sergi Palencia', '/players/sergi-palencia-hurtado/314218/', '', '', '', 'D', 'https://a/b/2023/03/26/c/d/e/f/123456/', 'W', 'https://a/b/2023/03/26/c/d/e/f/123456/', '', '', 2.16, '', '', '/players/cole-bassett/496345/', 23, 'https://secure.cache.images.core.optasports.com/soccer/players/18x18/496345.png', 'C. Bassett', '', 21, 'M', 442, 5, 5, 0, 1, 0, 1, 0, 1, 0, 0, '', '', '/players/kwadwo-opoku/675119/', 22.0, 'https://secure.cache.images.core.optasports.com/soccer/players/18x18/675119.png', 'K. Opoku', '', 21, 'A', 335, 4, 4, 0, 1, 0, 1, 0, 0, 0, 0, '', 'Weather', 'nuvens dispersas', '', 'Wins', '0', '', 'Wins', '3', '', '/players/jon-gallagher/532849/', '/players/alhassan-abubakar/445377/', '/players/carlos-akapo-martinez/226778/', '', '/players/sergi-palencia-hurtado/314218/', '/players/jackson-ragen/483475/', '/players/aaron-long/335436/']]

url = 'https://stackoverflow.com'

webAppsUrl = "https://script.google.com/macros/s/XXXXX/exec"
params = {
    'pylist': json.dumps(df_list),
    'okgo': url
}
web_app_response = requests.get(
    webAppsUrl, headers=headers, params=params, timeout=360
)
print(web_app_response.text)

Google Apps Script完整代码 #

function doGet(e) {
  const lock = LockService.getDocumentLock();
  if (lock.tryLock(360000)) {
    try {
      const ss = SpreadsheetApp.getActive();

      let clrrg = Sheets.newBatchClearValuesRequest();
      clrrg.ranges = ['All Python!A1:BW'];
      Sheets.Spreadsheets.Values.batchClear(clrrg,ss.getId());

      var pylist = JSON.parse(e.parameter.pylist);
      var allpy_sheet = SpreadsheetApp.getActive().getSheetByName('All Python');
      allpy_sheet.getRange(1, 1, pylist.length, pylist[0].length).setValues(pylist);

      lock.releaseLock();
      return ContentService.createTextOutput('Done!');
    } catch (error) {
      lock.releaseLock();
      const errorObj = {
        message: error.message,
        stack: error.stack
      };
      const folder = DriveApp.getFoldersByName("Error GAS").next();
      const file = folder.createFile(
        new Date().toString() + '.txt',
        JSON.stringify(errorObj, null, 2)
      );
      return ContentService.createTextOutput(error);
    }
  } else {
    return ContentService.createTextOutput('Timeout!');
  }
}

错误信息 #

<HTML>
<HEAD>
<TITLE>Bad Request</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Bad Request</H1>
<H2>Error 400</H2>
</BODY>
</HTML>

内容的提问来源于stack exchange，提问作者Digital Farmer