Google Sheets BatchUpdate报Invalid value错误求助排查

Let's break down your problem and walk through practical fixes, since the error points to specific provider names being incorrectly interpreted as google.protobuf.ListValue instead of plain strings.

Problem Recap #

When using the Google Sheets API batch update, the values Netent, Play'n Go, and Pragmatic trigger an error stating they're invalid values of type google.protobuf.ListValue. Other strings (like slot game names) work fine, and removing these provider names makes the request succeed. Your request structure looks correct, and logs show what appears to be valid string arrays—but the API is seeing something unexpected.

Key Troubleshooting Steps #

• Verify the actual structure of your request data
  Console logs might hide nested structures or unexpected object types. Add a detailed log of the full request data right before sending it to the API to expose any hidden issues:

  const requestData = [
    getDocsData(`${title}!C7:C`, vars.slots),
    getDocsData(`${title}!D7:D`, vars.providers),
  ];
  // Log the full, stringified data to reveal hidden structure
  console.log("Full Request Data:", JSON.stringify(requestData, null, 2));
  
  // Proceed with the batchUpdate
  sheets.spreadsheets.values.batchUpdate({
    spreadsheetId: 'secret url',
    resource: {
      data: requestData,
      valueInputOption: 'USER_ENTERED'
    },
    auth: oAuth_client
  }, (err, res) => { ... });
  

  Look closely at the values array for the provider range—if any entry isn't a plain string (e.g., ["Netent"] becomes {"values": ["Netent"]}), that's the root cause.

• Inspect the getDocsData function implementation
  The issue is almost certainly in how getDocsData processes vars.providers. Since slot game names work but provider names don't, the function might be treating provider values differently:

  • Does vars.providers contain ListValue objects instead of raw strings?
  • Does getDocsData have logic that converts certain strings into structured objects (e.g., parsing provider names as special values)?

  If vars.providers comes from another API or database that returns ListValue types, you'll need to extract the actual string value from those objects before passing them to the Sheets API.

• Test with a minimal, hardcoded request
  Rule out API-level issues by sending a direct, hardcoded batch update with the problematic values:

  // Minimal test request to isolate the issue
  sheets.spreadsheets.values.batchUpdate({
    spreadsheetId: 'secret url',
    resource: {
      data: [
        {
          range: 'Kopie van Bonushunt Template!D7:D',
          values: [['Netent'], ['Play\'n Go'], ['Pragmatic']]
        }
      ],
      valueInputOption: 'USER_ENTERED'
    },
    auth: oAuth_client
  }, (err, res) => {
    if (err) console.error("Test Error:", err);
    else console.log("Test Success: Values updated correctly");
  });
  

  If this test succeeds, the problem is definitely in your getDocsData function or vars.providers data source. If it fails, try switching valueInputOption to RAW—this skips Sheets' value parsing and might reveal if the strings are being misinterpreted as formulas or special commands.

• Validate vars.providers data types
  Before passing vars.providers to getDocsData, check the type of each element to ensure they're raw strings:

  console.log("Providers data type check:");
  vars.providers.forEach((provider, index) => {
    console.log(`Provider ${index}:`, provider, "Type:", typeof provider);
    // Log object structure if it's not a string
    if (typeof provider === 'object') console.log("Object structure:", JSON.stringify(provider));
  });
  

  If any provider is an object (not a string), extract the string value from it (e.g., provider.values[0].stringValue or similar, depending on the object's structure).

Likely Fix #

Once you confirm that getDocsData is returning ListValue objects instead of raw strings, modify the function to extract the string values. For example, if each provider entry is a ListValue with a values array, adjust the function to return:

// Example fix inside getDocsData for ListValue objects
values: providerList.map(item => [item.values[0].stringValue])

If vars.providers already has raw strings, ensure getDocsData isn't wrapping them in unnecessary object structures.

内容的提问来源于stack exchange，提问作者Ilja KO