如何修改R脚本实现谷歌表格指定路径存储与每日两次重命名下载

Got it, let's adjust your script to save the sheet to your specified path, rename the file dynamically, and set it up for twice-daily runs. Here's the updated code:

# Load required libraries
library(dplyr)
library(data.table)
library(googlesheets)
library(rJava)

# Define target directory (uses file.path to avoid path escape issues)
target_dir <- file.path("E:", "My_data", "File")

# Create directory if it doesn't exist
if (!dir.exists(target_dir)) {
  dir.create(target_dir, recursive = TRUE)
  message("Created target directory: ", target_dir)
}

# Authenticate (uncomment and run once to set up credentials)
# gs_auth(new_user = TRUE)
# gs_ls() # Verify you can access the sheet

# Access the Google Sheet
gs_obj <- gs_title("XYZ")

# Generate dynamic filename with timestamp (to distinguish twice-daily runs)
# Format: XYZ_YYYYMMDD_HHMM.csv (e.g., XYZ_20240520_0930.csv)
timestamp <- format(Sys.time(), "%Y%m%d_%H%M")
output_file <- file.path(target_dir, paste0("XYZ_", timestamp, ".csv"))

# Download the sheet to the specified path with custom filename
gs_download(gs_obj, to = output_file, overwrite = TRUE)

message("Successfully downloaded sheet to: ", output_file)

Key Modifications Explained: #

• Specified Target Path: Used file.path() to construct the path safely (avoids issues with backslashes on Windows). Added a check to create the directory if it doesn't exist, so you won't get errors if the folder is new.
• Dynamic File Renaming: Added a timestamp with date and time (YYYYMMDD_HHMM) to the filename. This ensures each of your twice-daily downloads gets a unique name, so you won't overwrite previous versions.
• Directed Download: Updated gs_download() to use the to parameter, which lets you specify the full path and filename for the saved file.

Setting Up Twice-Daily Runs: #

To make this script run automatically twice a day, use your OS's scheduling tool:

• Windows: Open Task Scheduler, create two separate tasks (or one task with two triggers) set to run at your desired times (e.g., 9 AM and 5 PM). For the action, select "Start a program" and point to your Rscript.exe (usually in C:\Program Files\R\R-x.x.x\bin\Rscript.exe), with the argument being the full path to your saved R script.
• Linux/macOS: Use cron to add two time entries. Open your crontab with crontab -e and add lines like:

  0 9 * * * /usr/bin/Rscript /path/to/your/download_script.R
  0 17 * * * /usr/bin/Rscript /path/to/your/download_script.R
  

  This runs the script at 9 AM and 5 PM every day. Adjust the times to fit your needs.

Note: #

If you're using a newer version of Google Sheets integration, consider switching to the googlesheets4 package (the successor to googlesheets) for better long-term support. But the above script works with your current library setup.

内容的提问来源于stack exchange，提问作者Rahul shah