Скачать или смотреть How to Resolve the Service Spreadsheet Failed Error in Google Apps Script

Learn how to fix the common Google Apps Script error when accessing CSV files in Google Sheets, along with a practical solution and example code!
---
This video is based on the question https://stackoverflow.com/q/66390709/ asked by the user 'Gregory Givens' ( https://stackoverflow.com/u/15291677/ ) and on the answer https://stackoverflow.com/a/66445939/ provided by the user 'Jason E.' ( https://stackoverflow.com/u/14606038/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Error Exception: Service Spreadsheet failed while accessing document with id

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Resolving the Service Spreadsheet Failed Error in Google Apps Script

If you've encountered the dreaded error message: Service Spreadsheet failed while accessing document with id, you’re not alone. This issue often arises when you attempt to access a file using Google Apps Script, especially when working with Google Sheets and CSV files. In this post, we will explore the reasons behind this error and provide a clear, easy-to-follow solution to help you get back on track.

Understanding the Problem

Several users have experienced problems when trying to access a CSV file using Google Apps Script. Specifically, the attempts to access a CSV file (which has a .csv extension) through script commands like openById or openByUrl have turned out unsuccessful. Confusion grows when users confirm that they can access the file directly from Google Drive.

Common Issues Leading to the Error

Here’s a concise summary of the issues typically encountered:

The file is present in Google Drive, but cannot be accessed via Apps Script.

Both openById and openByUrl methods fail to open the file.

The script produces the same error even when attempting to access a Google Sheets URL.

Users might assume that a CSV file can be handled the same way as a Google Sheets file in Apps Script.

The Root of the Problem

The root cause of this issue is simple: CSV files cannot be directly accessed as spreadsheets in Google Apps Script. Whether using a file ID or URL, Apps Script does not recognize CSVs as spreadsheets. To work with the data contained in a CSV file, you need to parse it and transfer the data into a Google Sheet.

A Practical Solution: Parsing CSV Files

To effectively utilize a CSV file using Google Apps Script, you need to read and parse the content before inserting it into a Google Sheet. Here’s how to do it:

Step-by-Step Instructions

Get the File: Use the filename to locate the CSV in Google Drive.

Parse the CSV: Read and convert the CSV data into a usable format.

Insert Data into Sheet: Place the parsed data into an active spreadsheet.

Sample Code

Here’s a sample script you can use to achieve this:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

DriveApp.getFilesByName(): This method finds the file by its name in Google Drive.

Utilities.parseCsv(): This utility converts the CSV file data into a 2D array format suitable for Google Sheets.

getRange() and setValues(): These methods work together to populate the spreadsheet with the parsed CSV data.

Conclusion

By following the steps outlined above, you can successfully read data from a CSV file and insert it into a Google Spreadsheet using Google Apps Script. This process bypasses the limitation of direct CSV access and allows you to manipulate your data effectively. If you continue to encounter issues, double-check your file names and ensure that correct permissions are set for Google Drive access.

Don't let script errors slow you down! Use the provided solution to streamline your workflow and efficiently handle CSV files in your Google Apps Script projects. Happy scripting!