iTranslated by AI

The content below is an AI-generated translation. This is an experimental feature, and may contain errors. View original article
🐷
Using Google Sheets in C++ (Without Firebase)

2022/08/08に公開
C++
Google
tech
 To first introduce Google SheetsFundamentally, to use Google Sheets—or rather, any Google API—there is security protection in place, and multiple authentication methods are provided to pass through it.
API Key
OAuth
Service Account
In this article, we will use "OAuth", but I will explain all authentication methods for reference.

 API KeyOfficial Guide: Using API Keys
API Key has more restrictions compared to other authentication methods (OAuth or Service Account).

It varies depending on the API used, but in the case of Google Sheets, you cannot perform write operations.

 Authentication MethodUsage is simple; for Google Sheets, authentication is completed just by including it as a query string in the URL (http://example.com/hogehoge?key=APIKEY).

 Service AccountService Account
To be honest, I tried researching and using this, but the official explanation didn't include the fine-grained technical details needed to implement it in C++, so I cannot explain it in depth.

In the case of Google Sheets, you can manipulate spreadsheets by registering the email address generated by this method as if it were a single user.

 Benefits and Use CasesI use this when running Bots; it allows operations to run under the Bot's unique account permissions rather than the individual developer's account permissions. Therefore, even if the developer's account has "Viewer" permissions, the Bot can write if its account has "Editor" permissions. (Conversely, if the Bot is a "Viewer," it cannot write.)

 OAuthOAuth

 Benefits and Use CasesFor this explanation, I will adopt this method. However, let me explain.

From a convenience perspective, OAuth allows operations based on the permissions of the account belonging to the person who logged in through it. Thus, while Service Accounts are for automated operations like Bots, OAuth is suitable for software intended to be manually launched by humans.

 Authentication MethodAuthentication is performed using HTTP POST.

There is a mechanism involving authentication codes. However, it is not that difficult. I think it will be very easy to understand if you take a look at the official explanation once before reading this article. In fact, it's at the level of "you absolutely must look at it." It helps tremendously when troubleshooting.

 Let's Try Logging In Using OAuthI have researched how to implement it without using an OAuth library for this explanation, but since there is an OAuth library for C++, I will use that instead. Since the library comes as a set with HTTP communication functionality, introducing OAuth is easy.

 1. Installing cpprestsdkWe will install the very popular cpprestsdk. Since I am developing with VS2022 this time, I installed it via vcpkg.
The official vcpkg page and installation instructions can be found here, so please install it accordingly.

 2. Creating the OAuth Login PartI say "creating"... but actually, sample source code for an OAuth2 client is provided in cpprestsdk.
I'd like to use this for logging in... but there's only one thing to add to this source code, so I'll just write that here. Otherwise, please copy the sample source code as is.
class Google_session : public oauth2_session_sample
{
public:
Google_session()
 : oauth2_session_sample(U("Google"),
    GoogleOAuth::client_id,
    GoogleOAuth::client_secret,
    GoogleOAuth::auth_uri,
    GoogleOAuth::token_uri,
    GoogleOAuth::redirect_uris)
{
 /// Add this line
 m_oauth2_config.set_scope(L"https://www.googleapis.com/auth/spreadsheets");
}

};
This time, I only added m_oauth2_config.set_scope. Note that what is written here pertains to the "permissions required for the task." A list of scopes is provided here, so please fill it in based on the OAuth information generated in Google Cloud beforehand.
By the way, it goes without saying, but please do not include things like your client_id in source code stored on GitHub or elsewhere.

 3. Fetching DataI'd like to use spreadsheets.get to view spreadsheet data. To that end, I'll quickly write the following code.
By the way, there is a function called s_TextConvert in the source code; this is used for converting std::string to std::wstring and vice versa. If you don't need it, feel free to remove it. There is a page with an excellent explanation, so please refer to it.
Google_DataSheet Google_session::getCells(std::string SheetsID, std::string TableName, std::wstring cell) {

	web::uri t_uri = std::wstring(U("/v4/spreadsheets/") + s_TextConvert(SheetsID));

	uri_builder builder = uri_builder(t_uri);

	builder.append_query(U("ranges"), s_TextConvert(TableName) + U("!") + cell);
	builder.append_query(U("includeGridData"), U("true"));

	auto text_url = (U("https://sheets.googleapis.com") + builder.to_string());

	http_client client(text_url, m_http_config);

	http_request requestData(methods::GET);
	requestData.headers().add(L"Accept", L"application/json");

	int error_code = 0;

	auto requestTask = client.request(requestData).then([&](http_response response) {
		std::cout << "[GoogleSheet] Received response status code: " << response.status_code() << " <= SheetID : " << SheetsID << std::endl;
		error_code = response.status_code();

		return response.extract_json();
		}).then([&](json::value body_value) ->Google_DataSheet {

			if (error_code != 200) return Google_DataSheet();

			auto test_getString = [&](std::wstring key) -> std::wstring {
				json::value value = body_value.at(key);
				if (value.is_string() == false) {
					return U("");
				}
				return value.as_string();
			};
			Google_DataSheet cellData = Google_DataSheet();
			try {
				// Get sheet info.
				cellData.spreadsheetId = s_TextConvert(test_getString(U("spreadsheetId")));

				cellData.SheetName = s_TextConvert(std::wstring(body_value.at(U("properties")).at(L"title").as_string()));

				cellData.tableName = s_TextConvert(std::wstring(body_value.at(U("sheets")).as_array().at(0).at(U("properties")).at(U("title")).as_string()));

				// Get cell info
				auto cells = body_value.at(U("sheets")).as_array().at(0).at(U("data")).as_array().at(0).at(U("rowData")).as_array();
				for (int i = 0; i < cells.size(); i++) {
					if ( !cells.at(i).at(U("values")).as_array().at(0).at(U("formattedValue")).is_string() ) {
						break;
					}

					cellData.cells.push_back(s_TextConvert(std::wstring(cells.at(i).at(U("values")).as_array().at(0).at(U("formattedValue")).as_string())));
				}

				return cellData;
			}
			catch (const std::exception& e)
			{
				return cellData;
			}
			return cellData;
			});

		try
		{
			requestTask.wait();
			if (requestTask.get().cells.size() == 0) {
				std::cout << "[GoogleSheets HTTP ERROR1] : " << SheetsID << std::endl;
			}
			else {
				//std::cout << "[GoogleSheets HTTP OK] : " << SheetsID << std::endl;
			}
			Sleep(1000);
			return requestTask.get();
		}
		catch (const std::exception& e)
		{
			std::cout << "[GoogleSheets HTTP ERROR2] : " << SheetsID << std::endl;

			Sleep(1000);
			return requestTask.get();
		}

		return Google_DataSheet();
}

 ExplanationIt goes without saying, but in the following source code, we generate the URL. By using builder.append_query, you can register query strings cleanly. (Since it's a simple mechanism, string manipulation would work, but this looks much cleaner, doesn't it?)
web::uri t_uri = std::wstring(U("/v4/spreadsheets/") + s_TextConvert(SheetsID));

uri_builder builder = uri_builder(t_uri);

builder.append_query(U("ranges"), s_TextConvert(TableName) + U("!") + cell);
builder.append_query(U("includeGridData"), U("true"));
In the code below, after the HTTP communication, we convert the body to JSON to extract information. Please read this regarding the structure of the response.
// Get sheet info
cellData.spreadsheetId = s_TextConvert(test_getString(U("spreadsheetId")));

cellData.SheetName = s_TextConvert(std::wstring(body_value.at(U("properties")).at(L"title").as_string()));

cellData.tableName = s_TextConvert(std::wstring(body_value.at(U("sheets")).as_array().at(0).at(U("properties")).at(U("title")).as_string()));

// Get cell info
auto cells = body_value.at(U("sheets")).as_array().at(0).at(U("data")).as_array().at(0).at(U("rowData")).as_array();
for (int i = 0; i < cells.size(); i++) {
	if ( !cells.at(i).at(U("values")).as_array().at(0).at(U("formattedValue")).is_string() ) {
		break;
	}
}
With this, we have successfully fetched the data.

Now, as a bonus, let's try adding some data.

 4. Adding DataThis time, I wrote a process to add data row by row using spreadsheets.values.append.

It works by adding three values to the cell variable, which then adds those three values horizontally in a single row.
bool Google_session::AddCells(std::string SheetsID, std::string TableName,  std::vector<std::wstring> cell) {

	web::uri t_uri = std::wstring(U("/v4/spreadsheets/") + s_TextConvert(SheetsID) + U("/values/") + U("A1:append"));

	uri_builder builder = uri_builder(t_uri);

	builder.append_query(U("valueInputOption"), U("USER_ENTERED"));
	builder.append_query(U("insertDataOption"), U("INSERT_ROWS"));

	auto text_url = (U("https://sheets.googleapis.com") + builder.to_string());

	std::wcout << text_url << std::endl;

	http_client client(text_url, m_http_config);

	http_request requestData(methods::POST);
	requestData.headers().add(L"Accept", L"application/json");
	requestData.headers().add(L"Content-Type", L"application/json");

	//  ***************  Dataset  *************** 
	auto values = json::value::array(cell.size());
	for (int i = 0; i < cell.size(); i++) {
		values.as_array().at(i) = json::value::string(cell.at(i));

	}

	auto values_out = json::value::array(1);
	values_out.as_array().at(0) = values;

	std::vector<std::pair<::utility::string_t, json::value>> fields;
	fields.push_back(std::pair<::utility::string_t, json::value>(U("majorDimension"), json::value::string(U("ROWS"))));
	fields.push_back(std::pair<::utility::string_t, json::value>(U("range"), json::value::string(U("A1"))));
	fields.push_back(std::pair<::utility::string_t, json::value>(U("values"), values_out));

	auto top = json::value::object(fields);

	//std::wcout << top.to_string() << std::endl;
	//  ***************************************** 

	requestData.set_body(top);

	int error_code = 0;

	auto requestTask = client.request(requestData).then([&](http_response response) {
		std::cout << "[GoogleSheet] Received response status code: " << response.status_code() << " <= SheetID : " << SheetsID << std::endl;
		error_code = response.status_code();

		return response.extract_json();
		}).then([&](json::value body_value) -> bool {

			if (error_code != 200) return false;

			auto test_getString = [&](std::wstring key) -> std::wstring {
				json::value value = body_value.at(key);
				if (value.is_string() == false) {
					return U("");
				}
				return value.as_string();
			};

			try {
				// I probably should capture something here, but it's a hassle, so I won't ☆
				return true;
			}
			catch (const std::exception& e)
			{
				//std::cout << e.what() << std::endl;
				return false;
			}
			return false;
			});
		try
		{
			requestTask.wait();
			if (requestTask.get() == false) {
				std::cout << "[GoogleSheets HTTP ERROR1] : " << SheetsID << std::endl;
			}
			else {
				//std::cout << "[GoogleSheets HTTP OK] : " << SheetsID << std::endl;
			}
			Sleep(1000);
			return true;
		}
		catch (const std::exception& e)
		{
			std::cout << "[GoogleSheets HTTP ERROR2] : " << SheetsID << " : HTTP Code" << e.what() << std::endl;

			Sleep(1000);
			return false;
		}

		return false;
}

 ExplanationHere, we are configuring the Body settings, which were not required during the Get operation. It will work correctly if configured properly.
//  ***************  Dataset  *************** 
auto values = json::value::array(cell.size());
for (int i = 0; i < cell.size(); i++) {
	values.as_array().at(i) = json::value::string(cell.at(i));

}

auto values_out = json::value::array(1);
values_out.as_array().at(0) = values;

std::vector<std::pair<::utility::string_t, json::value>> fields;
fields.push_back(std::pair<::utility::string_t, json::value>(U("majorDimension"), json::value::string(U("ROWS"))));
fields.push_back(std::pair<::utility::string_t, json::value>(U("range"), json::value::string(U("A1"))));
fields.push_back(std::pair<::utility::string_t, json::value>(U("values"), values_out));

auto top = json::value::object(fields);

//std::wcout << top.to_string() << std::endl;
//  *****************************************

 Deleting DataFinally, let's try deleting data.

For deletion, we use deleteDimension within spreadsheets.batchUpdate.

It works by taking an index and deleting the corresponding row.
bool Google_session::RemoveColumn(std::string SheetsID, std::string TableName, int index) {

	web::uri t_uri = std::wstring(U("/v4/spreadsheets/") + s_TextConvert(SheetsID) + U(":batchUpdate"));

	uri_builder builder = uri_builder(t_uri);

	auto text_url = (U("https://sheets.googleapis.com") + builder.to_string());

	std::cout << s_TextConvert( text_url ) << std::endl;

	http_client client(text_url, m_http_config);

	http_request requestData(methods::POST);
	requestData.headers().add(L"Accept", L"application/json");
	requestData.headers().add(L"Content-Type", L"application/json");

	//  ***************  Dataset  *************** 


	std::vector<std::pair<::utility::string_t, json::value>> fields;
	fields.push_back(std::pair<::utility::string_t, json::value>(U("dimension"), json::value::string(U("ROWS"))));
	fields.push_back(std::pair<::utility::string_t, json::value>(U("startIndex"), index ));
	fields.push_back(std::pair<::utility::string_t, json::value>(U("endIndex"), index + 1));

	std::vector<std::pair<::utility::string_t, json::value>> range;
	range.push_back(std::pair<::utility::string_t, json::value>(U("range"), json::value::object(fields)));


	std::vector<std::pair<::utility::string_t, json::value>> deleteDimension ;
	deleteDimension.push_back(	std::pair<::utility::string_t, json::value>(U("deleteDimension"), json::value::object(range)) );

	auto top_requests = json::value::array(1);
	top_requests.at(0) = json::value::object(deleteDimension);

	std::vector<std::pair<::utility::string_t, json::value>> top_requests_vec;
	top_requests_vec.push_back(std::pair<::utility::string_t, json::value>(U("requests"), top_requests));

	auto top = json::value::object(top_requests_vec);

	//std::cout << s_TextConvert( top.to_string() ) << std::endl;
	//  ***************************************** 

	requestData.set_body(top);

	int error_code = 0;

	auto requestTask = client.request(requestData).then([&](http_response response) {
		std::cout << "[GoogleSheet] Received response status code: " << response.status_code() << " <= SheetID : " << SheetsID << std::endl;
		error_code = response.status_code();

		return response.extract_json();
		}).then([&](json::value body_value) -> bool {

		
			if (error_code != 200) return false;

			auto test_getString = [&](std::wstring key) -> std::wstring {
				json::value value = body_value.at(key);
				if (value.is_string() == false) {
					return U("");
				}
				return value.as_string();
			};

			try {
				// I probably should capture something here, but it's a hassle, so I won't ☆
				return true;
			}
			catch (const std::exception& e)
			{
				//std::cout << e.what() << std::endl;
				return false;
			}
			return false;
		});

	try
	{
		requestTask.wait();
		if (requestTask.get() == false) {
			std::cout << "[GoogleSheets HTTP ERROR1] : " << SheetsID << std::endl;
		}
		else {
			//std::cout << "[GoogleSheets HTTP OK] : " << SheetsID << std::endl;
		}
		Sleep(1000);
		return true;
	}
	catch (const std::exception& e)
	{
		std::cout << "[GoogleSheets HTTP ERROR2] : " << SheetsID << " : HTTP Code" << e.what() << std::endl;

		Sleep(1000);
			
		return false;
	}
		
	return false;
}

 The EndThat was easy, right! When people say "OAuth," it has this feeling of being incredibly complex, but when you actually try it out, the structure is surprisingly clean!

I've used Google Sheets libraries for Python and JS before, but... to be honest... being totally blunt, I felt that hitting the HTTP API directly like this was more intuitive and easier to use... I ended up with a profound sense of defeat.

Also, since spreadsheets.batchUpdate has so many features, I can't help but feel that hitting the HTTP endpoints directly offers a richer variety of options... (´ε｀；) Hmm...
iTranslated by AI

To first introduce Google Sheets

API Key

Authentication Method

Service Account

Benefits and Use Cases

OAuth

Benefits and Use Cases

Authentication Method

Let's Try Logging In Using OAuth

1. Installing cpprestsdk

2. Creating the OAuth Login Part

3. Fetching Data

Explanation

4. Adding Data

Explanation

Deleting Data

The End

Discussion
