xls_rs/
google_sheets.rs

1//! Google Sheets API handler for reading and writing Google Sheets
2
3use crate::config::Config;
4use crate::csv_handler::CellRange;
5use crate::traits::{DataReader, DataWriteOptions, DataWriter, FileHandler};
6use anyhow::{anyhow, Context, Result};
7use tokio::runtime::Runtime;
8
9/// Handler for Google Sheets operations
10pub struct GoogleSheetsHandler {
11    config: Config,
12    rt: Runtime,
13}
14
15impl GoogleSheetsHandler {
16    /// Create a new Google Sheets handler
17    pub fn new() -> Self {
18        Self {
19            config: Config::default(),
20            rt: Runtime::new().expect("Failed to create tokio runtime"),
21        }
22    }
23
24    /// Create a new Google Sheets handler with custom config
25    pub fn with_config(config: Config) -> Self {
26        Self {
27            config,
28            rt: Runtime::new().expect("Failed to create tokio runtime"),
29        }
30    }
31
32    /// Parse Google Sheets URL or ID to extract spreadsheet ID
33    pub fn parse_spreadsheet_id(&self, path: &str) -> Result<String> {
34        // If it's a gsheet:// protocol URL
35        if path.starts_with("gsheet://") {
36            let id = path
37                .strip_prefix("gsheet://")
38                .ok_or_else(|| anyhow!("Invalid gsheet URL"))?;
39            return Ok(id.split('/').next().unwrap_or(id).to_string());
40        }
41
42        // If it's a full Google Sheets URL, extract the ID
43        if path.starts_with("https://docs.google.com/spreadsheets/") {
44            if let Some(start) = path.find("/d/") {
45                let start = start + 3;
46                if let Some(end) = path[start..].find('/') {
47                    return Ok(path[start..start + end].to_string());
48                } else {
49                    return Ok(path[start..].to_string());
50                }
51            }
52        }
53
54        // Check if it's just the ID
55        if path.len() >= 44
56            && path
57                .chars()
58                .all(|c| c.is_alphanumeric() || c == '-' || c == '_')
59        {
60            return Ok(path.to_string());
61        }
62
63        Err(anyhow!("Invalid Google Sheets URL or ID: {}", path))
64    }
65
66    /// Parse sheet name from path
67    pub fn parse_sheet_name(&self, path: &str) -> Option<String> {
68        // Extract from gsheets://id/sheet_name
69        if path.starts_with("gsheet://") {
70            let parts: Vec<&str> = path[9..].split('/').collect();
71            if parts.len() > 1 {
72                return Some(parts[1].to_string());
73            }
74        }
75
76        None
77    }
78
79    /// Convert A1 notation to row/column indices
80    pub fn a1_to_row_col(&self, a1: &str) -> Result<(usize, usize)> {
81        let mut col_start = 0;
82        let mut row_start = 0;
83
84        // Find where letters end and numbers begin
85        for (i, c) in a1.chars().enumerate() {
86            if c.is_alphabetic() {
87                col_start += 1;
88            } else if c.is_numeric() {
89                row_start = i;
90                break;
91            }
92        }
93
94        if col_start == 0 || row_start == 0 {
95            return Err(anyhow!("Invalid A1 notation: {}", a1));
96        }
97
98        // Parse column (base-26)
99        let col_str = &a1[..col_start];
100        let mut col = 0;
101        for c in col_str.chars() {
102            col = col * 26 + (c.to_ascii_uppercase() as u8 - b'A' + 1) as usize;
103        }
104        col -= 1; // Convert to 0-based
105
106        // Parse row
107        let row = a1[row_start..].parse::<usize>()? - 1; // Convert to 0-based
108
109        Ok((row, col))
110    }
111
112    /// Convert row/column indices to A1 notation
113    pub fn row_col_to_a1(&self, row: usize, col: usize) -> String {
114        let mut col = col + 1;
115        let mut col_str = String::new();
116
117        while col > 0 {
118            col -= 1;
119            col_str.insert(0, ((col % 26) as u8 + b'A') as char);
120            col /= 26;
121        }
122
123        format!("{}{}", col_str, row + 1)
124    }
125
126    /// Convert CellRange to A1 notation range
127    pub fn cell_range_to_a1(&self, range: &CellRange, sheet_name: Option<&str>) -> String {
128        let start = self.row_col_to_a1(range.start_row, range.start_col);
129        let end = self.row_col_to_a1(range.end_row, range.end_col);
130
131        let range_str = if start == end {
132            start
133        } else {
134            format!("{}:{}", start, end)
135        };
136
137        if let Some(name) = sheet_name {
138            format!("'{}'!{}", name, range_str)
139        } else {
140            range_str
141        }
142    }
143
144    /// List sheet titles for a spreadsheet using the Google Sheets API.
145    ///
146    /// Requires `google_sheets.api_key` in [`Config`] (suitable for public spreadsheets when the
147    /// Sheets API is enabled for the key).
148    pub fn list_sheet_titles(&self, spreadsheet_ref: &str) -> Result<Vec<String>> {
149        let api_key = self.config.google_sheets.api_key.as_deref().ok_or_else(|| {
150            anyhow!(
151                "google_sheets.api_key is not set in config; required to list sheet titles via the API"
152            )
153        })?;
154
155        let id = self.parse_spreadsheet_id(spreadsheet_ref)?;
156        let url = format!(
157            "https://sheets.googleapis.com/v4/spreadsheets/{id}?fields=sheets.properties.title&key={api_key}"
158        );
159
160        let resp = ureq::get(&url)
161            .call()
162            .map_err(|e| anyhow!("Google Sheets request failed: {}", e))?;
163
164        let status = resp.status();
165        let body = resp
166            .into_string()
167            .map_err(|e| anyhow!("Failed to read Sheets API response: {}", e))?;
168
169        if status != 200 {
170            anyhow::bail!("Google Sheets API returned HTTP {}: {}", status, body);
171        }
172
173        let v: serde_json::Value =
174            serde_json::from_str(&body).with_context(|| "Invalid JSON from Sheets API")?;
175
176        let sheets = v
177            .get("sheets")
178            .and_then(|s| s.as_array())
179            .ok_or_else(|| anyhow!("Sheets API response missing 'sheets' array"))?;
180
181        let mut titles = Vec::new();
182        for sheet in sheets {
183            if let Some(title) = sheet
184                .get("properties")
185                .and_then(|p| p.get("title"))
186                .and_then(|t| t.as_str())
187            {
188                titles.push(title.to_string());
189            }
190        }
191
192        Ok(titles)
193    }
194}
195
196impl Default for GoogleSheetsHandler {
197    fn default() -> Self {
198        Self::new()
199    }
200}
201
202impl DataReader for GoogleSheetsHandler {
203    fn read(&self, path: &str) -> Result<Vec<Vec<String>>> {
204        // For now, return a placeholder implementation
205        // In a real implementation, this would use the Google Sheets API
206        let _spreadsheet_id = self.parse_spreadsheet_id(path)?;
207        let _sheet_name = self.parse_sheet_name(path);
208
209        // Full read/write requires OAuth or service-account auth; not implemented here.
210        Ok(vec![
211            vec!["Column1".to_string(), "Column2".to_string()],
212            vec!["Value1".to_string(), "Value2".to_string()],
213        ])
214    }
215
216    fn read_with_headers(&self, path: &str) -> Result<Vec<Vec<String>>> {
217        self.read(path)
218    }
219
220    fn read_range(&self, path: &str, range: &CellRange) -> Result<Vec<Vec<String>>> {
221        let _spreadsheet_id = self.parse_spreadsheet_id(path)?;
222        let _sheet_name = self.parse_sheet_name(path);
223        let _range_str = self.cell_range_to_a1(range, _sheet_name.as_deref());
224
225        // Same as [`Self::read`]: live API access is not wired for this path yet.
226        Ok(vec![vec![
227            "RangeValue1".to_string(),
228            "RangeValue2".to_string(),
229        ]])
230    }
231
232    fn read_as_json(&self, path: &str) -> Result<String> {
233        let data = self.read(path)?;
234        serde_json::to_string_pretty(&data).map_err(Into::into)
235    }
236
237    fn supports_format(&self, path: &str) -> bool {
238        path.starts_with("gsheet://")
239            || path.starts_with("https://docs.google.com/spreadsheets/")
240            || (path.len() >= 44
241                && path
242                    .chars()
243                    .all(|c| c.is_alphanumeric() || c == '-' || c == '_'))
244    }
245}
246
247impl DataWriter for GoogleSheetsHandler {
248    fn write(&self, path: &str, data: &[Vec<String>], options: DataWriteOptions) -> Result<()> {
249        let spreadsheet_id = self.parse_spreadsheet_id(path)?;
250        let sheet_name = options.sheet_name.or_else(|| self.parse_sheet_name(path));
251
252        // Live API write not implemented; logs intent only.
253        println!("Writing to Google Sheets: {}", spreadsheet_id);
254        if let Some(name) = &sheet_name {
255            println!("Sheet: {}", name);
256        }
257        println!("Data rows: {}", data.len());
258
259        Ok(())
260    }
261
262    fn write_range(
263        &self,
264        path: &str,
265        data: &[Vec<String>],
266        start_row: usize,
267        start_col: usize,
268    ) -> Result<()> {
269        let spreadsheet_id = self.parse_spreadsheet_id(path)?;
270        let sheet_name = self.parse_sheet_name(path);
271        let start_a1 = self.row_col_to_a1(start_row, start_col);
272
273        // Live API write not implemented; logs intent only.
274        println!("Writing range to Google Sheets: {}", spreadsheet_id);
275        if let Some(name) = &sheet_name {
276            println!("Sheet: {}", name);
277        }
278        println!("Start: {}", start_a1);
279        println!("Data rows: {}", data.len());
280
281        Ok(())
282    }
283
284    fn append(&self, path: &str, data: &[Vec<String>]) -> Result<()> {
285        let spreadsheet_id = self.parse_spreadsheet_id(path)?;
286        let sheet_name = self.parse_sheet_name(path);
287
288        // Live API append not implemented; logs intent only.
289        println!("Appending to Google Sheets: {}", spreadsheet_id);
290        if let Some(name) = &sheet_name {
291            println!("Sheet: {}", name);
292        }
293        println!("Data rows: {}", data.len());
294
295        Ok(())
296    }
297
298    fn supports_format(&self, path: &str) -> bool {
299        path.starts_with("gsheet://")
300            || path.starts_with("https://docs.google.com/spreadsheets/")
301            || (path.len() >= 44
302                && path
303                    .chars()
304                    .all(|c| c.is_alphanumeric() || c == '-' || c == '_'))
305    }
306}
307
308impl FileHandler for GoogleSheetsHandler {
309    fn format_name(&self) -> &'static str {
310        "gsheet"
311    }
312
313    fn supported_extensions(&self) -> &'static [&'static str] {
314        &["gsheet"]
315    }
316}
317
318impl Clone for GoogleSheetsHandler {
319    fn clone(&self) -> Self {
320        Self {
321            config: self.config.clone(),
322            rt: Runtime::new().expect("Failed to create tokio runtime"),
323        }
324    }
325}
xls_rs/google_sheets.rs

xls_rs/
google_sheets.rs
