Google Sheets Native Fix: Custom Apps Script #

Since IMPORTDATA() doesn’t support data URLs directly, you can build a custom function to decode the base64 CSV content and load it into your sheet. Here’s how to make it work:

1. Open your Google Sheet, go to Extensions > Apps Script to launch the script editor.
2. Replace the default code with this custom function:

function GETDATAFROMDATAURL(dataUrl) {
  // Extract the base64 portion of the data URL
  const base64Content = dataUrl.split('base64,')[1];
  if (!base64Content) throw new Error("Invalid data URL format");
  
  // Decode base64 to raw CSV text
  const csvText = Utilities.newBlob(Utilities.base64Decode(base64Content)).getDataAsString();
  
  // Split CSV into rows and columns for sheet compatibility
  const rows = csvText.split('\n').filter(row => row.trim() !== '');
  return rows.map(row => row.split(','));
}

3. Save the script (name it something like DataURLImporter) and close the editor.
4. Back in your sheet, use the function in any cell like this:

=GETDATAFROMDATAURL("data:text/csv;base64,RXZlbnRSZXNpZGVudENvdW50eSxJbmRpdmlkdWFscyBUZXN0ZWQsSW5kaXZpZHVhbHMgUG9zaXRpdmUsVG90YWwgUmVjb3ZlcmVkLFRvdGFsIERlYXRocw0KUG9sayw4MTA4Niw5NDc5LDY1NjQsMjAxDQpMaW5uLDMxMjg5LDIwMDUsMTU3Miw4Nw0KSm9obnNvbiwyMzMyOSwxODgxLDEzNjMsMTUNCkJsYWNrIEhhd2ssMjMyODQsMjk3MCwyMjQ2LDYyDQpTY290dCwyMjk")

5. Set up auto-updates:
   • Return to the Apps Script editor, click Triggers > Add Trigger.
   • Select GETDATAFROMDATAURL as the function, set the event source to Time-driven, and pick your preferred update frequency (hourly, daily, etc.).
   • You’ll need to authorize the script to access your sheet when prompted.

Alternative Tools for Auto-Updating Datasets #

If you’re open to moving beyond Google Sheets, these options work well for scheduled, automated data imports:

Airtable #

• Use Airtable’s Scripting app to write a base64 decoding script, then set up an Automation to run it on a schedule. The script can directly insert decoded CSV rows into your table.
• For no-code setups, use tools like Make or Zapier to fetch the data URL, decode the base64 content, and update your Airtable table automatically on a schedule.

Power BI #

• In Power BI Desktop, go to Get Data > Blank Query, open the Power Query Editor, and add this M code to decode the data URL:

let
    DataURL = "data:text/csv;base64,RXZlbnRSZXNpZGVudENvdW50eSxJbmRpdmlkdWFscyBUZXN0ZWQsSW5kaXZpZHVhbHMgUG9zaXRpdmUsVG90YWwgUmVjb3ZlcmVkLFRvdGFsIERlYXRocw0KUG9sayw4MTA4Niw5NDc5LDY1NjQsMjAxDQpMaW5uLDMxMjg5LDIwMDUsMTU3Miw4Nw0KSm9obnNvbiwyMzMyOSwxODgxLDEzNjMsMTUNCkJsYWNrIEhhd2ssMjMyODQsMjk3MCwyMjQ2LDYyDQpTY290dCwyMjk",
    Base64Part = Text.AfterDelimiter(DataURL, "base64,"),
    DecodedBytes = Binary.FromBase64(Base64Part),
    CSVText = Text.FromBinary(DecodedBytes),
    ParsedCSV = Csv.Document(CSVText)
in
    ParsedCSV

• Publish the report to Power BI Service, then set up a Refresh Schedule to keep the data updated automatically.

Python Script with Cloud Scheduling #

• Write a simple Python script to decode the data URL, process the CSV, and save it to cloud storage (like Google Drive or AWS S3):

import base64
from googleapiclient.discovery import build
from google.oauth2 import service_account
from googleapiclient.http import MediaFileUpload

# Decode the data URL
data_url = "data:text/csv;base64,RXZlbnRSZXNpZGVudENvdW50eSxJbmRpdmlkdWFscyBUZXN0ZWQsSW5kaXZpZHVhbHMgUG9zaXRpdmUsVG90YWwgUmVjb3ZlcmVkLFRvdGFsIERlYXRocw0KUG9sayw4MTA4Niw5NDc5LDY1NjQsMjAxDQpMaW5uLDMxMjg5LDIwMDUsMTU3Miw4Nw0KSm9obnNvbiwyMzMyOSwxODgxLDEzNjMsMTUNCkJsYWNrIEhhd2ssMjMyODQsMjk3MCwyMjQ2LDYyDQpTY290dCwyMjk"
base64_content = data_url.split('base64,')[1]
csv_content = base64.b64decode(base64_content).decode('utf-8')

# Save to a local CSV file
with open('updated_data.csv', 'w') as f:
    f.write(csv_content)

# Upload to Google Drive (requires service account credentials)
credentials = service_account.Credentials.from_service_account_file('your-credentials.json')
drive_service = build('drive', 'v3', credentials=credentials)

file_metadata = {'name': 'updated_data.csv'}
media = MediaFileUpload('updated_data.csv', mimetype='text/csv')
drive_service.files().create(body=file_metadata, media_body=media, fields='id').execute()

• Use cloud scheduling tools like Google Cloud Scheduler or AWS CloudWatch Events to run the script on your desired schedule. You can then connect this CSV back to Google Sheets with IMPORTDATA() if needed.

内容的提问来源于stack exchange，提问作者Jake Tornholm