\u5b9a\u671f\u5b9f\u884c\u3084\u30a8\u30e9\u30fc\u30cf\u30f3\u30c9\u30ea\u30f3\u30b0\u3092\u7d44\u307f\u5408\u308f\u305b\u308b\u3053\u3068\u3067\u3001\u5b9f\u52d9\u3067\u4f7f\u3048\u308b\u5805\u7262\u306a\u81ea\u52d5\u5316\u30b7\u30b9\u30c6\u30e0\u3092\u69cb\u7bc9\u3067\u304d\u307e\u3059\u3002<\/b><\/span>\u5b9f\u8df5\u7684\u306a\u6d3b\u7528\u4f8b\u3092\u7d39\u4ecb\u3057\u307e\u3059\u3002<\/p>\n\u5b9a\u671f\u5b9f\u884c\u30b9\u30af\u30ea\u30d7\u30c8\u306e\u4f5c\u6210<\/h3>\n
\u58f2\u4e0a\u30ec\u30dd\u30fc\u30c8\u81ea\u52d5\u751f\u6210\uff1a<\/p>\n
import schedule<\/p>\n
import time<\/p>\n
from datetime import datetime<\/p>\n
def daily_report():<\/p>\n
try:<\/p>\n
# \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3092\u958b\u304f<\/p>\n
spreadsheet = client.open(‘\u58f2\u4e0a\u7ba1\u7406’)<\/p>\n
worksheet = spreadsheet.worksheet(‘\u65e5\u6b21\u30ec\u30dd\u30fc\u30c8’)<\/p>\n
# \u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u304b\u3089\u58f2\u4e0a\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\uff08\u4eee\u60f3\u7684\u306a\u4f8b\uff09<\/p>\n
sales_data = get_sales_data_from_db()<\/p>\n
# \u30ec\u30dd\u30fc\u30c8\u306e\u4f5c\u6210<\/p>\n
report = [<\/p>\n
[f”\u65e5\u6b21\u58f2\u4e0a\u30ec\u30dd\u30fc\u30c8 – {datetime.now().strftime(‘%Y-%m-%d’)}”],<\/p>\n
[‘\u5546\u54c1\u540d’, ‘\u8ca9\u58f2\u6570’, ‘\u58f2\u4e0a\u91d1\u984d’],]<\/p>\n
report.extend(sales_data)<\/p>\n
# \u5408\u8a08\u884c\u306e\u8ffd\u52a0<\/p>\n
total_sales = sum([row[2] for row in sales_data])<\/p>\n
report.append([‘\u5408\u8a08’, ”, total_sales])<\/p>\n
# \u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306b\u66f8\u304d\u8fbc\u307f<\/p>\n
worksheet.clear()<\/p>\n
worksheet.update(‘A1’, report)<\/p>\n
print(f”\u30ec\u30dd\u30fc\u30c8\u4f5c\u6210\u5b8c\u4e86: {datetime.now()}”)<\/p>\n
except Exception as e:<\/p>\n
print(f”\u30a8\u30e9\u30fc\u767a\u751f: {e}”)<\/p>\n
send_error_notification(str(e))<\/p>\n
# \u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u8a2d\u5b9a<\/p>\n
schedule.every().day.at(“09:00”).do(daily_report)<\/p>\n
# \u5b9f\u884c\u30eb\u30fc\u30d7<\/p>\n
while True:<\/p>\n
schedule.run_pending()<\/p>\n
time.sleep(60)<\/p>\n
\u8907\u6570\u30b7\u30fc\u30c8\u306e\u7d71\u5408\u51e6\u7406<\/h3>\n
\u30c7\u30fc\u30bf\u96c6\u7d04\u30b9\u30af\u30ea\u30d7\u30c8\uff1a<\/p>\n
def consolidate_sheets():<\/p>\n
# \u8907\u6570\u306e\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u304b\u3089\u30c7\u30fc\u30bf\u3092\u53ce\u96c6<\/p>\n
sheet_names = [‘\u6771\u4eac\u652f\u5e97’, ‘\u5927\u962a\u652f\u5e97’, ‘\u540d\u53e4\u5c4b\u652f\u5e97’]<\/p>\n
all_data = []<\/p>\n
for sheet_name in sheet_names:<\/p>\n
try:<\/p>\n
spreadsheet = client.open(sheet_name)<\/p>\n
worksheet = spreadsheet.sheet1<\/p>\n
# \u30d8\u30c3\u30c0\u30fc\u3092\u9664\u304f\u30c7\u30fc\u30bf\u3092\u53d6\u5f97<\/p>\n
data = worksheet.get_all_values()[1:]<\/p>\n
# \u652f\u5e97\u540d\u3092\u8ffd\u52a0<\/p>\n
for row in data:<\/p>\n
row.insert(0, sheet_name)<\/p>\n
all_data.extend(data)<\/p>\n
except Exception as e:<\/p>\n
print(f”{sheet_name}\u306e\u51e6\u7406\u3067\u30a8\u30e9\u30fc: {e}”)<\/p>\n
# \u7d71\u5408\u30b7\u30fc\u30c8\u306b\u66f8\u304d\u8fbc\u307f<\/p>\n
master_sheet = client.open(‘\u7d71\u5408\u7ba1\u7406\u8868’)<\/p>\n
master_worksheet = master_sheet.sheet1<\/p>\n
# \u30d8\u30c3\u30c0\u30fc\u306e\u4f5c\u6210<\/p>\n
headers = [‘\u652f\u5e97\u540d’, ‘\u65e5\u4ed8’, ‘\u5546\u54c1’, ‘\u6570\u91cf’, ‘\u91d1\u984d’]<\/p>\n
all_data.insert(0, headers)<\/p>\n
# \u30c7\u30fc\u30bf\u306e\u66f8\u304d\u8fbc\u307f<\/p>\n
master_worksheet.clear()<\/p>\n
master_worksheet.update(‘A1’, all_data)<\/p>\n
return len(all_data) – 1\u00a0 # \u30c7\u30fc\u30bf\u4ef6\u6570\u3092\u8fd4\u3059<\/p>\n
\u30a8\u30e9\u30fc\u30cf\u30f3\u30c9\u30ea\u30f3\u30b0\u3068\u30ed\u30b0\u8a18\u9332<\/h3>\n
\u5b89\u5b9a\u3057\u305f\u52d5\u4f5c\u3092\u5b9f\u73fe\u3059\u308b\u4ed5\u7d44\u307f\uff1a<\/p>\n
import logging<\/p>\n
from typing import Optional, List, Dict<\/p>\n
# \u30ed\u30b0\u8a2d\u5b9a<\/p>\n
logging.basicConfig(<\/p>\n
filename=’spreadsheet_operations.log’,<\/p>\n
level=logging.INFO,<\/p>\n
format=’%(asctime)s – %(levelname)s – %(message)s’)<\/p>\n
class SpreadsheetManager:<\/p>\n
def __init__(self, credentials_file: str):<\/p>\n
self.client = self._initialize_client(credentials_file)<\/p>\n
self.retry_count = 3<\/p>\n
def _initialize_client(self, credentials_file: str):<\/p>\n
try:<\/p>\n
scope = [<\/p>\n
‘https:\/\/www.googleapis.com\/auth\/spreadsheets’,<\/p>\n
‘https:\/\/www.googleapis.com\/auth\/drive’]<\/p>\n
credentials = Credentials.from_service_account_file(<\/p>\n
credentials_file,<\/p>\n
scopes=scope)<\/p>\n
return gspread.authorize(credentials)<\/p>\n
except Exception as e:<\/p>\n
logging.error(f”\u8a8d\u8a3c\u30a8\u30e9\u30fc: {e}”)<\/p>\n
raise<\/p>\n
def safe_update(self,<\/p>\n
sheet_name: str,<\/p>\n
range_name: str,<\/p>\n
values: List[List]) -> bool:<\/p>\n
“””<\/p>\n
\u5b89\u5168\u306a\u66f4\u65b0\u51e6\u7406\uff08\u30ea\u30c8\u30e9\u30a4\u6a5f\u80fd\u4ed8\u304d\uff09<\/p>\n
“””<\/p>\n
for attempt in range(self.retry_count):<\/p>\n
try:<\/p>\n
spreadsheet = self.client.open(sheet_name)<\/p>\n
worksheet = spreadsheet.sheet1<\/p>\n
worksheet.update(range_name, values)<\/p>\n
logging.info(f”\u66f4\u65b0\u6210\u529f: {sheet_name} – {range_name}”)<\/p>\n
return True<\/p>\n
except gspread.exceptions.APIError as e:<\/p>\n
if e.response.status_code == 429:\u00a0 # Rate limit<\/p>\n
wait_time = 2 ** attempt<\/p>\n
logging.warning(f”Rate limit. {wait_time}\u79d2\u5f85\u6a5f…”)<\/p>\n
time.sleep(wait_time)<\/p>\n
else:<\/p>\n
logging.error(f”API\u30a8\u30e9\u30fc: {e}”)<\/p>\n
break<\/p>\n
except Exception as e:<\/p>\n
logging.error(f”\u4e88\u671f\u3057\u306a\u3044\u30a8\u30e9\u30fc: {e}”)<\/p>\n
break<\/p>\n
return False<\/p>\n
\u5927\u91cf\u306e\u30c7\u30fc\u30bf\u3092\u6271\u3046\u969b\u306e\u30c6\u30af\u30cb\u30c3\u30af<\/h2>\n
\u5927\u91cf\u306e\u30c7\u30fc\u30bf\u3092\u6271\u3046\u969b\u306f\u3001API\u306e\u5236\u9650\u3092\u8003\u616e\u3057\u3001\u52b9\u7387\u7684\u306a\u30b3\u30fc\u30c9\u3092\u66f8\u304f\u3053\u3068\u304c\u91cd\u8981\u3067\u3059\u3002<\/b><\/span>\u5b9f\u52d9\u3067\u4f7f\u3048\u308b\u30c6\u30af\u30cb\u30c3\u30af\u3092\u7d39\u4ecb\u3057\u307e\u3059\u3002<\/p>\nAPI\u30ec\u30fc\u30c8\u5236\u9650\u3078\u306e\u5bfe\u5fdc<\/h3>\n
\u5236\u9650\u56de\u907f\u306e\u30c6\u30af\u30cb\u30c3\u30af\uff1a<\/p>\n
\n\n\n| \u5236\u9650\u9805\u76ee<\/th>\n | \u4e0a\u9650<\/th>\n | \u5bfe\u7b56<\/th>\n<\/tr>\n<\/thead>\n |
\n\n| \u8aad\u307f\u53d6\u308a<\/td>\n | 100\u30ea\u30af\u30a8\u30b9\u30c8\/100\u79d2<\/td>\n | \u30d0\u30c3\u30c1\u8aad\u307f\u53d6\u308a\u3092\u4f7f\u7528<\/td>\n<\/tr>\n |
\n| \u66f8\u304d\u8fbc\u307f<\/td>\n | 100\u30ea\u30af\u30a8\u30b9\u30c8\/100\u79d2<\/td>\n | \u30d0\u30c3\u30c1\u66f4\u65b0\u3092\u4f7f\u7528<\/td>\n<\/tr>\n |
\n| \u30bb\u30eb\u6570<\/td>\n | 1000\u4e07\u30bb\u30eb\/\u30b7\u30fc\u30c8<\/td>\n | \u30c7\u30fc\u30bf\u3092\u5206\u5272<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \u5b9f\u88c5\u4f8b\uff1a<\/p>\n import time<\/p>\n from typing import List<\/p>\n def batch_process_with_delay(data_chunks: List, delay: float = 1.0):<\/p>\n “””<\/p>\n \u30c1\u30e3\u30f3\u30af\u3054\u3068\u306b\u9045\u5ef6\u3092\u5165\u308c\u306a\u304c\u3089\u51e6\u7406<\/p>\n “””<\/p>\n for i, chunk in enumerate(data_chunks):<\/p>\n process_chunk(chunk)<\/p>\n # \u6700\u5f8c\u306e\u30c1\u30e3\u30f3\u30af\u3067\u306a\u3051\u308c\u3070\u5f85\u6a5f<\/p>\n if i < len(data_chunks) – 1:<\/p>\n time.sleep(delay)<\/p>\n \u30ad\u30e3\u30c3\u30b7\u30e5\u306e\u6d3b\u7528<\/h3>\n\u30c7\u30fc\u30bf\u30ad\u30e3\u30c3\u30b7\u30e5\u306e\u5b9f\u88c5\uff1a<\/p>\n import pickle<\/p>\n from datetime import datetime, timedelta<\/p>\n class DataCache:<\/p>\n def __init__(self, cache_file=’cache.pkl’):<\/p>\n self.cache_file = cache_file<\/p>\n self.cache_duration = timedelta(hours=1)<\/p>\n def get_or_fetch(self, key: str, fetch_function):<\/p>\n # \u30ad\u30e3\u30c3\u30b7\u30e5\u306e\u8aad\u307f\u8fbc\u307f<\/p>\n cache = self._load_cache()<\/p>\n # \u30ad\u30e3\u30c3\u30b7\u30e5\u306e\u6709\u52b9\u6027\u78ba\u8a8d<\/p>\n if key in cache:<\/p>\n data, timestamp = cache[key]<\/p>\n if datetime.now() – timestamp < self.cache_duration:<\/p>\n return data<\/p>\n # \u65b0\u898f\u53d6\u5f97\u3068\u30ad\u30e3\u30c3\u30b7\u30e5\u4fdd\u5b58<\/p>\n data = fetch_function()<\/p>\n cache[key] = (data, datetime.now())<\/p>\n self._save_cache(cache)<\/p>\n return data<\/p>\n \u30bb\u30ad\u30e5\u30ea\u30c6\u30a3\u3068\u30c7\u30fc\u30bf\u4fdd\u8b77\u306e\u8003\u616e\u4e8b\u9805<\/h2>\n\u8a8d\u8a3c\u60c5\u5831\u306e\u7ba1\u7406\u306f\u6975\u3081\u3066\u91cd\u8981\u3067\u3059\u3002<\/b><\/span>JSON\u30ad\u30fc\u30d5\u30a1\u30a4\u30eb\u306f\u7d76\u5bfe\u306bGit\u30ea\u30dd\u30b8\u30c8\u30ea\u306b\u30b3\u30df\u30c3\u30c8\u305b\u305a\u3001\u74b0\u5883\u5909\u6570\u3084\u79d8\u5bc6\u7ba1\u7406\u30b5\u30fc\u30d3\u30b9\u3092\u4f7f\u7528\u3057\u3066\u4fdd\u8b77\u3057\u307e\u3059\u3002\u307e\u305f\u3001\u6700\u5c0f\u6a29\u9650\u306e\u539f\u5247\u306b\u5f93\u3044\u3001\u5fc5\u8981\u6700\u5c0f\u9650\u306e\u30b9\u30b3\u30fc\u30d7\u306e\u307f\u3092\u8981\u6c42\u3059\u308b\u3053\u3068\u304c\u63a8\u5968\u3055\u308c\u307e\u3059\u3002<\/p>\n\u30c7\u30fc\u30bf\u306e\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3082\u5fd8\u308c\u305a\u306b\u5b9f\u88c5\u3057\u307e\u3057\u3087\u3046\u3002\u91cd\u8981\u306a\u66f4\u65b0\u524d\u306b\u306f\u5fc5\u305a\u30d0\u30c3\u30af\u30a2\u30c3\u30d7\u3092\u53d6\u5f97\u3057\u3001\u30a8\u30e9\u30fc\u6642\u306b\u30ed\u30fc\u30eb\u30d0\u30c3\u30af\u3067\u304d\u308b\u4ed5\u7d44\u307f\u3092\u6e96\u5099\u3059\u308b\u3053\u3068\u304c\u91cd\u8981\u3067\u3059\u3002<\/p>\n Python\u3068\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8API\u9023\u643a\u3067\u696d\u52d9\u3092\u81ea\u52d5\u5316<\/h2>\nPython\u3068Google Sheets API\u3092\u7d44\u307f\u5408\u308f\u305b\u308b\u3053\u3068\u3067\u3001\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u306e\u66f4\u65b0\u3084\u96c6\u8a08\u3001\u30ec\u30dd\u30fc\u30c8\u4f5c\u6210\u3092\u81ea\u52d5\u5316\u3057\u3001\u65e5\u5e38\u696d\u52d9\u306e\u52b9\u7387\u3092\u5927\u5e45\u306b\u9ad8\u3081\u3089\u308c\u307e\u3059\u3002gspread\u306a\u3069\u306e\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u5229\u7528\u3059\u308c\u3070\u3001\u77ed\u3044\u30b3\u30fc\u30c9\u3067\u30c7\u30fc\u30bf\u306e\u8aad\u307f\u66f8\u304d\u3084\u6574\u5f62\u304c\u53ef\u80fd\u3067\u3059\u3002\u3055\u3089\u306b\u3001\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u5b9f\u884c\u3084\u30a8\u30e9\u30fc\u30cf\u30f3\u30c9\u30ea\u30f3\u30b0\u3092\u7d44\u307f\u5408\u308f\u305b\u308b\u3053\u3068\u3067\u3001\u5b89\u5b9a\u3057\u305f\u81ea\u52d5\u51e6\u7406\u304c\u5b9f\u73fe\u3057\u307e\u3059\u3002<\/p>\n API\u306e\u5236\u9650\u3084\u30bb\u30ad\u30e5\u30ea\u30c6\u30a3\u5bfe\u7b56\u3092\u7406\u89e3\u3057\u305f\u4e0a\u3067\u6700\u9069\u5316\u3092\u884c\u3046\u3053\u3068\u3067\u3001\u4fe1\u983c\u6027\u306e\u9ad8\u3044\u81ea\u52d5\u5316\u30b7\u30b9\u30c6\u30e0\u3092\u69cb\u7bc9\u3067\u304d\u308b\u3067\u3057\u3087\u3046\u3002<\/p>\n","protected":false},"excerpt":{"rendered":" Python\u3067Google\u30b9\u30d7\u30ec\u30c3\u30c9\u30b7\u30fc\u30c8\u3092\u64cd\u4f5c\u3059\u308c\u3070\u3001\u624b\u52d5\u5165\u529b\u3084\u96c6\u8a08\u4f5c\u696d\u3092\u81ea\u52d5\u5316\u3057\u3001\u30ec\u30dd\u30fc\u30c8\u4f5c\u6210\u3084\u30c7\u30fc\u30bf\u5206\u6790\u3092\u52b9\u7387\u5316\u3067\u304d\u307e\u3059\u3002Google Sheets API\u3084gspread\u30e9\u30a4\u30d6\u30e9\u30ea\u3092\u4f7f\u7528\u3059\u308b\u3053\u3068\u3067\u3001\u30d7\u30ed\u30b0\u30e9\u30e0\u304b\u3089 […]<\/p>\n","protected":false},"author":25,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[51],"tags":[],"class_list":["post-21627","post","type-post","status-publish","format-standard","hentry","category-excel"],"acf":{"pdf-dlbox-url":"","templatesbox-title":"\u306e\u30c6\u30f3\u30d7\u30ec\u30fc\u30c8","templatesbox-img1-url":"","templatesbox-img1-title":"","templatesbox-img2-url":"","templatesbox-img2-title":"","templatesbox-desc":"\u306e\u30c6\u30f3\u30d7\u30ec\u30fc\u30c8\u3092\u7121\u6599\u3067\u63d0\u4f9b\u3057\u3066\u3044\u307e\u3059\u3002\u305c\u3072\u3054\u81ea\u7531\u306b\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u3057\u3066\u6d3b\u7528\u304f\u3060\u3055\u3044\u3002","templatesbox-btn-text":"\u7121\u6599\u3067\u30c0\u30a6\u30f3\u30ed\u30fc\u30c9\u3059\u308b","templatesbox-link":""},"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/posts\/21627","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/users\/25"}],"replies":[{"embeddable":true,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/comments?post=21627"}],"version-history":[{"count":10,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/posts\/21627\/revisions"}],"predecessor-version":[{"id":21763,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/posts\/21627\/revisions\/21763"}],"wp:attachment":[{"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/media?parent=21627"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/categories?post=21627"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/biz.moneyforward.com\/work-efficiency\/basic\/wp-json\/wp\/v2\/tags?post=21627"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |