如何通过R脚本将ggplot2图表插入Google Sheets？可借助googlesheets包吗？

Absolutely! You can definitely insert ggplot2 charts (or any image for that matter) into Google Sheets using R scripts. The older googlesheets package is no longer maintained, so we’ll use its modern replacement—googlesheets4—alongside ggplot2 to get this done smoothly. Here's a step-by-step guide:

Step 1: Install and Load Required Packages #

First, install the necessary packages if you haven’t already, then load them into your R session:

install.packages(c("googlesheets4", "ggplot2"))
library(googlesheets4)
library(ggplot2)

Step 2: Authenticate with Google #

You’ll need to grant R access to your Google account to interact with your Sheets. Run this command and follow the browser prompts to log in and authorize access:

gs4_auth()

Step 3: Create a ggplot2 Chart and Save It Locally #

Let’s make a sample plot first, then save it as a PNG file. We’ll use a temporary file here to avoid cluttering your system, but you can use a permanent file path too:

# Create a sample ggplot2 chart
my_plot <- ggplot(mtcars, aes(x = hp, y = mpg)) +
  geom_point(color = "darkred") +
  geom_smooth(method = "lm", se = FALSE) +
  labs(title = "Horsepower vs. MPG", x = "Horsepower", y = "Miles per Gallon")

# Save the plot to a temporary PNG file
temp_img <- tempfile(fileext = ".png")
ggsave(temp_img, plot = my_plot, width = 6, height = 4, dpi = 150)

Step 4: Insert the Image into Your Google Sheet #

Use the gs4_insert_image() function to add the saved image to your target Sheet. You’ll need either the Sheet’s URL or its unique ID, plus the cell where you want the image to start (e.g., "B2"):

# Replace this with your Google Sheet's URL or ID
target_sheet <- "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID_HERE/edit"

# Insert the image starting at cell B2
gs4_insert_image(ss = target_sheet,
                 range = "B2",
                 image = temp_img)

Bonus Tips #

• Adjust the image size directly in the gs4_insert_image() call using the width and height arguments (values are in inches).
• If you want to keep the plot file long-term, replace tempfile() with a specific path like "my_plot.png".
• Ensure your Google account has edit access to the Sheet you’re modifying.
• If you run into authentication issues, try gs4_auth(reset = TRUE) to re-authenticate your account.

内容的提问来源于stack exchange，提问作者Sam Firke