LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@manymanyuni(manymanyuni)

Rust+OAuth+REST API で Google Spreadsheet のセル値の取得と更新

Last updated at Posted at 2024-08-03

Rust で OAuth のアクセストークンを使ってスプレッドシートの範囲の値の取得と更新をするプログラムを書いたので、簡単に載せておく。

↓は JSON レスポンスを受け取る用の型（QuickType で生成した）

spreadsheets.rs
#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct UpdateCellInput {
    pub value_input_option: String,
    pub data: Vec<ValueRange>,
    pub include_values_in_response: bool,
    pub response_value_render_option: String,
    pub response_date_time_render_option: String,
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct ValueRange {
    pub range: String,
    pub major_dimension: String,
    pub values: Vec<Vec<Value>>,
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(untagged)]
pub enum Value {
    Bool(bool),
    Integer(i64),
    String(String),
}

impl fmt::Display for Value {
    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
        match self {
            Self::Bool(b) => {
                write!(f, "{}", b)
            },
            Self::Integer(i) => {
                write!(f, "{}", i)
            },
            Self::String(s) => {
                write!(f, "{}", s)
            },
        }
    }
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct UpdateCellResult {
    pub spreadsheet_id: String,
    pub updated_range: String,
    pub updated_rows: i64,
    pub updated_columns: i64,
    pub updated_cells: i64,
    pub updated_data: Option<ValueRange>,
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct UpdateCellBatchResult {
    pub spreadsheet_id: String,
    pub total_updated_rows: i32,
    pub total_updated_columns: i32,
    pub total_updated_cells: i32,
    pub total_updated_sheets: i32,
    pub responses: Vec<Response>,
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct Response {
    pub spreadsheet_id: String,
    pub updated_range: String,
    pub updated_rows: i32,
    pub updated_columns: i32,
    pub updated_cells: i32,
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
pub struct SSError {
    pub code: i32,
    pub message: String,
    pub status: String,
}

impl fmt::Display for SSError {
    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
        write!(f, "code={}, message={}, status={}", self.code, self.message, self.status)
    }
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
pub struct RequestError {
    pub error: SSError,
}

impl fmt::Display for RequestError {
    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
        write!(f, "{}", self.error)
    }
}

impl std::error::Error for RequestError {
    fn source(&self) -> Option<&(dyn std::error::Error + 'static)> {
        None
    }
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct GetCellBatchResult {
    pub spreadsheet_id: String,
    pub value_ranges: Vec<ValueRange>
}

セルレンジの取得は以下

spreadsheets.rs
pub async fn get_cell(token: &str, sheet_id: &str, range: &str) -> Result<ValueRange, Box<dyn std::error::Error + Send + Sync>> {
    let client = reqwest::Client::builder()
        .timeout(Duration::from_secs(15))
        .build()?;
    let result = client.get(format!("https://sheets.googleapis.com/v4/spreadsheets/{}/values/{}?majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE&dateTimeRenderOption=FORMATTED_STRING", sheet_id, range))
        .header(reqwest::header::AUTHORIZATION, format!("Bearer {}", token))
        .send()
        .await?;

    match result.status() {
        StatusCode::OK => {
            Ok(result.json().await?)
        },
        _ => {
            Err(Box::new(result.json::<RequestError>().await?))
        },
    }
}

pub async fn get_cell_batch(token: &str, sheet_id: &str, range: &str) -> Result<GetCellBatchResult, Box<dyn std::error::Error + Send + Sync>> {
    let client = reqwest::Client::builder()
        .timeout(Duration::from_secs(15))
        .build()?;
    let result = client.get(format!("https://sheets.googleapis.com/v4/spreadsheets/{}/values:batchGet?ranges={}&majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE&dateTimeRenderOption=FORMATTED_STRING", sheet_id, range))
        .header(reqwest::header::AUTHORIZATION, format!("Bearer {}", token))
        .send()
        .await?;

    match result.status() {
        StatusCode::OK => {
            Ok(result.json().await?)
        },
        _ => {
            Err(Box::new(result.json::<RequestError>().await?))
        },
    }
}

更新は以下

spreadsheets.rs
pub async fn update_cell(token: &str, sheet_id: &str, range: &str, values: Vec<Value>) -> Result<UpdateCellResult, Box<dyn std::error::Error + Send + Sync>> {
    let input = ValueRange {
        range: range.to_string(),
        major_dimension: "ROWS".to_string(),
        values: vec![values]
    };
    let input_json = serde_json::to_string(&input)?;

    let client = reqwest::Client::builder()
        .timeout(Duration::from_secs(15))
        .build()?;
    let result = client.put(format!("https://sheets.googleapis.com/v4/spreadsheets/{}/values/{}?valueInputOption=USER_ENTERED&includeValuesInResponse=false&responseValueRenderOption=FORMATTED_VALUE&responseDateTimeRenderOption=FORMATTED_STRING", sheet_id, range))
        .header(reqwest::header::AUTHORIZATION, format!("Bearer {}", token))
        .body(input_json)
        .send()
        .await?;

    match result.status() {
        StatusCode::OK => {
            Ok(result.json().await?)
        },
        _ => {
            Err(Box::new(result.json::<RequestError>().await?))
        },
    }
}

pub async fn update_cell_batch(token: &str, sheet_id: &str, range: &str, values: Vec<Value>) -> Result<UpdateCellBatchResult, Box<dyn std::error::Error + Send + Sync>> {
    let input = UpdateCellInput {
        value_input_option: "USER_ENTERED".to_string(),
        data: vec![ValueRange {
            range: range.to_string(),
            major_dimension: "ROWS".to_string(),
            values: vec![values],
        }],
        include_values_in_response: false,
        response_value_render_option: "FORMATTED_VALUE".to_string(),
        response_date_time_render_option: "FORMATTED_STRING".to_string(),
    };
    let input_json = serde_json::to_string(&input)?;

    let client = reqwest::Client::builder()
        .timeout(Duration::from_secs(15))
        .build()?;
    let result = client.post(format!("https://sheets.googleapis.com/v4/spreadsheets/{}/values:batchUpdate", sheet_id))
        .header(reqwest::header::AUTHORIZATION, format!("Bearer {}", token))
        .body(input_json)
        .send()
        .await?;

    match result.status() {
        StatusCode::OK => {
            Ok(result.json().await?)
        },
        _ => {
            Err(Box::new(result.json::<RequestError>().await?))
        },
    }
}

Google OAuth のアクセストークンの更新は以下

oauth.rs
#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
pub struct RefreshResult {
    pub access_token: String,
    pub expires_in: u32,
    pub scope: String,
    pub token_type: String,
}

pub async fn refresh_access_token(refresh_token: &str, client_id: &str, client_secret: &str, redirect_uri: &str) -> Result<RefreshResult, Box<dyn std::error::Error + Send + Sync>> {
    let mut params = HashMap::new();

    params.insert("refresh_token", refresh_token);
    params.insert("client_id", client_id);
    params.insert("client_secret", client_secret);
    params.insert("grant_type", "refresh_token");
    params.insert("redirect_uri", redirect_uri);

    let client = reqwest::Client::builder()
        .timeout(Duration::from_secs(15))
        .build()?;
    let result = client.post("https://www.googleapis.com/oauth2/v4/token")
        .form(&params)
        .send()
        .await?
        .json::<RefreshResult>()
        .await?;

    Ok(result)
}
1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?