serde_sheets/
lib.rs

1use csv::{ReaderBuilder, StringRecord, Writer, WriterBuilder};
2use google_sheets4::{
3    api::{ClearValuesRequest, ValueRange},
4    Sheets,
5};
6use serde::de::DeserializeOwned;
7use std::path::PathBuf;
8use thiserror::Error;
9use yup_oauth2::{ServiceAccountAuthenticator, ServiceAccountKey};
10
11#[derive(Error, Debug)]
12pub enum SheetsError {
13    #[error("SERVICE_ACCOUNT_JSON not defined")]
14    EnvVarNotFound(#[from] std::env::VarError),
15
16    #[error("Invalid service account JSON")]
17    InvalidServiceAccountJSON(#[from] serde_json::Error),
18
19    #[error("Error with token cache path")]
20    TokenCachePathError(#[from] std::io::Error),
21
22    #[error(transparent)]
23    SheetsError(#[from] google_sheets4::Error),
24
25    #[error(transparent)]
26    CSVError(#[from] csv::Error),
27
28    #[error("Internal error")]
29    InternalUTFError(#[from] std::string::FromUtf8Error),
30
31    #[error("Internal error")]
32    InternalWriterError(#[from] csv::IntoInnerError<Writer<Vec<u8>>>),
33}
34
35/// Builds a `ServiceAccountKey` from JSON in environment variable `SERVICE_ACCOUNT_JSON`
36pub fn service_account_from_env() -> Result<ServiceAccountKey, SheetsError> {
37    let env = std::env::var("SERVICE_ACCOUNT_JSON")?;
38    let key = serde_json::from_str(&env)?;
39    Ok(key)
40}
41
42/// Given a `ServiceAccountKey`, builds a `google_sheets4::Sheets` client, with
43/// access token cache at `token_cache_path` (if specified)
44pub async fn get_sheets<P: Into<PathBuf>>(
45    service_account: ServiceAccountKey,
46    token_cache_path: Option<P>,
47) -> Result<Sheets, SheetsError> {
48    let builder = ServiceAccountAuthenticator::builder(service_account);
49    let auth = if let Some(path) = token_cache_path {
50        builder.persist_tokens_to_disk(path).build().await?
51    } else {
52        builder.build().await?
53    };
54    let connector = hyper_rustls::HttpsConnectorBuilder::new()
55        .with_native_roots()
56        .https_only()
57        .enable_http1()
58        .enable_http2()
59        .build();
60    let sheets = Sheets::new(hyper::Client::builder().build(connector), auth);
61    Ok(sheets)
62}
63
64/// Clear all data from the sheet called `tab_name` in document `document_id`
65pub async fn clear_tab(
66    sheets: &mut Sheets,
67    document_id: &str,
68    tab_name: &str,
69) -> Result<(), SheetsError> {
70    sheets
71        .spreadsheets()
72        .values_clear(ClearValuesRequest::default(), document_id, tab_name)
73        .doit()
74        .await?;
75    Ok(())
76}
77
78/// Serialize a list of objects and write to the tab `tab_name` in document `document_id`.
79/// The sheet will be cleared before writing.
80pub async fn write_page(
81    sheets: &mut Sheets,
82    document_id: &str,
83    tab_name: &str,
84    objects: &[impl serde::Serialize],
85) -> Result<(), SheetsError> {
86    clear_tab(sheets, document_id, tab_name).await?;
87
88    let mut wtr = WriterBuilder::new().from_writer(vec![]);
89
90    for obj in objects {
91        wtr.serialize(&obj)?;
92    }
93
94    let data = String::from_utf8(wtr.into_inner()?)?;
95
96    let mut rdr = ReaderBuilder::new()
97        .has_headers(false)
98        .from_reader(data.as_bytes());
99    let records = rdr
100        .records()
101        .collect::<Result<Vec<StringRecord>, csv::Error>>()?;
102
103    let req = ValueRange {
104        major_dimension: None,
105        range: Some(tab_name.to_string()),
106        values: Some(
107            records
108                .into_iter()
109                .map(|s| s.iter().map(|s| s.to_string()).collect())
110                .collect(),
111        ),
112    };
113
114    sheets
115        .spreadsheets()
116        .values_update(req, document_id, tab_name)
117        .value_input_option("USER_ENTERED")
118        .include_values_in_response(false)
119        .doit()
120        .await?;
121
122    Ok(())
123}
124
125/// Append a single object `obj` to tab `tab_name` in document `document_id`
126pub async fn append_row(
127    sheets: &mut Sheets,
128    document_id: &str,
129    tab_name: &str,
130    obj: impl serde::Serialize,
131) -> Result<(), SheetsError> {
132    let mut wtr = WriterBuilder::new().from_writer(vec![]);
133
134    wtr.serialize(&obj)?;
135
136    let data = String::from_utf8(wtr.into_inner()?)?;
137
138    let mut rdr = ReaderBuilder::new()
139        .has_headers(true)
140        .from_reader(data.as_bytes());
141
142    let records = rdr
143        .records()
144        .collect::<Result<Vec<StringRecord>, csv::Error>>()?;
145
146    let req = ValueRange {
147        major_dimension: None,
148        range: Some(tab_name.to_string()),
149        values: Some(
150            records
151                .into_iter()
152                .map(|s| s.iter().map(|s| s.to_string()).collect())
153                .collect(),
154        ),
155    };
156
157    sheets
158        .spreadsheets()
159        .values_append(req, document_id, tab_name)
160        .value_input_option("USER_ENTERED")
161        .include_values_in_response(false)
162        .doit()
163        .await?;
164
165    Ok(())
166}
167
168/// Append a single object `obj` to tab `tab_name` in document `document_id`
169pub async fn read_all<T: DeserializeOwned>(
170    sheets: &mut Sheets,
171    document_id: &str,
172    tab_name: &str,
173) -> Result<Vec<T>, SheetsError> {
174    let (_body, value_range) = sheets
175        .spreadsheets()
176        .values_get(document_id, tab_name)
177        .doit()
178        .await?;
179
180    let rows = value_range.values.unwrap();
181    let mut wtr = WriterBuilder::new().from_writer(vec![]);
182
183    for row in rows {
184        if let Err(e) = wtr.write_record(&row) {
185            println!("error writing row- {:?}", e);
186        };
187    }
188
189    let data = String::from_utf8(wtr.into_inner()?)?;
190
191    let mut rdr = ReaderBuilder::new()
192        .has_headers(true)
193        .from_reader(data.as_bytes());
194
195    let mut records = vec![];
196    for result in rdr.deserialize() {
197        match result {
198            Ok(r) => records.push(r),
199            Err(e) => {
200                println!("error deserializing row: {:?}", e);
201            }
202        }
203    }
204
205    Ok(records)
206}
serde_sheets/lib.rs

serde_sheets/
lib.rs
