Myu: 畑管理GASについて

畑資材の支出入をGASで管理していたものに、機能を少し追加した。これまで畑管理は、ノートに書き込んでいたが、さすがに中身が増えてくると、整理がつきにくくなったので、これもGASで管理してみることにした。

　オープンデータのＡＰＩから天気、気温、降水量、風力など、自動で入力されるようにしました。畑日記のようなものです。

code.gs*******************
  const SPREADSHEET_ID = '***************';
const SHEET_NAME = 'Records2026'; // 年度毎変更可
const SHEET_NAME_EX='Expense2026'
const SHEET_YEAR='2026';
const GOOGLE_DRIVE = '***************';　//畑画像保存用

// 野菜マスタ（名前と単価）
const vegetablePrices = {
  "トマト": 100,
  "ナス": 40,
  "キュウリ": 40,
  "ピーマン": 20,
  "ゴーヤ": 60,
  "インゲン": 5,
  "スイカ": 1000,
  "末成りスイカ":200,
  "ミニトマト": 10,
  "サツマイモ": 130,
  "サトイモ": 40,
  "シソ": 5,
  "エダマメ": 10,
  "人参":40,
  "大根":100,
  "カブ":80
};

const CultivationRecordsPerspectives = {
  "栽培":0,  "技術":1,  "その他":2
};

function weatherText(code) {
  const map = {
    0: "晴",
    1: "晴れ時々曇",
    2: "曇",
    3: "曇",
    45: "霧",
    61: "弱雨",
    63: "雨",
    65: "強雨",
    71: "雪"
  };

return map[code] || "不明";
}

function doGet(e) {

const page = e.parameter.page || "index";

return HtmlService
    .createTemplateFromFile(page)
    .evaluate();
}

// HTML読み込み用
function include(filename) {
  return HtmlService
    .createHtmlOutputFromFile(filename)
    .getContent();
}

// 検索
function searchRecords(keywordText) {

const keywords = keywordText
    .trim()
    .toLowerCase()
    .split(/\s+/);

// 現在年
  let targetYear =
    String(new Date().getFullYear());

// yyyy取得
  const yearKeyword =
    keywords.find(k =>
      /^\d{4}$/.test(k)
    );

// 指定年あれば切替
  if(yearKeyword){

targetYear = yearKeyword;
  }

// スプレッドシート
  const ss =
    SpreadsheetApp.openById(
      SPREADSHEET_ID
    );

// 年シート
  const sheet =
    ss.getSheetByName(targetYear);

if(!sheet){

throw new Error(
      'シートなし: ' + targetYear
    );
  }

const values =
    sheet.getDataRange()
      .getValues();

// 年キーワード除外
  const searchKeywords =
    keywords.filter(k =>
      !/^\d{4}$/.test(k)
    );

const results = [];

for(let i = 1; i < values.length; i++){

const row = values[i];

const comment =
      String(row[7] || "")
        .toLowerCase();

const matched =
      searchKeywords.every(k =>
        comment.includes(k)
      );

if(!matched) continue;

results.push({

date:
        Utilities.formatDate(
          row[0],
          Session.getScriptTimeZone(),
          'yyyy-MM-dd'
        ),

weather:
        String(row[1] || ""),

comment:
        String(row[7] || ""),

image:
        String(row[8] || "")

});
  }

return results;
}

function getVegetables() {
  return vegetablePrices;
}

function getCultivationRecordsPerspectives() {
  return CultivationRecordsPerspectives;
}

function getWeatherOptions() {
  const options = [];

for (let i = 0; i <= 100; i++) {
    const text = weatherText(i);

if (text !== "不明" && !options.includes(text)) {
      options.push(text);
    }
  }

return options;
}

function getWeatherData(dateStr) {
  const latitude = **.**;//畑の　緯度　と　経度　を設定
  const longitude = ***.**;

const url =
    "https://api.open-meteo.com/v1/forecast" +
    "?latitude=" + latitude +
    "&longitude=" + longitude +
    "&hourly=temperature_2m,precipitation,wind_speed_10m,weathercode" +
    "&start_date=" + dateStr +
    "&end_date=" + dateStr +
    "&timezone=Asia%2FTokyo";

const response = UrlFetchApp.fetch(url);
  const data = JSON.parse(response.getContentText());

const temps = data.hourly.temperature_2m;
  const precs = data.hourly.precipitation;
  const winds = data.hourly.wind_speed_10m;
  const codes = data.hourly.weathercode;

const tempMax = Math.max(...temps);
  const tempMin = Math.min(...temps);
  const precipitation = precs.reduce((a, b) => a + b, 0);
  const windMax = Math.max(...winds);

// 最頻出天気コード
  const weatherCode = mode(codes);

return {
    weather: weatherText(weatherCode),
    tempMax: tempMax,
    tempMin: tempMin,
    precipitation: precipitation,
    windMax: windMax
  };
}

function mode(arr) {
  const count = {};
  let max = 0;
  let result = arr[0];

arr.forEach(v => {
    count[v] = (count[v] || 0) + 1;
    if (count[v] > max) {
      max = count[v];
      result = v;
    }
  });

return result;
}

function uploadImageToDrive(base64Data, fileName) { 
  const folder = DriveApp.getFolderById(GOOGLE_DRIVE);
  const contentType =
    base64Data.match(/^data:(.*);base64,/)[1];

const bytes =
    Utilities.base64Decode(
      base64Data.split(',')[1]
    );

const blob = Utilities.newBlob(
    bytes,
    contentType,
    fileName
  );

const file = folder.createFile(blob);

return file.getUrl();
}

function init(){
  const obj= getTotalSum();  // 合計金額取得 
  return JSON.stringify({
    totalSum: obj.sum 
    ,lastDay: obj.last
    ,totalSumEx: obj.sumex 
    ,lastDayEx: obj.lastex
    ,balance:obj.balance
      });
}

function addRecord(data) {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  // const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEET_NAME);
  
  if (!sheet) {
    Logger.log('シート名「' + SHEET_NAME + '」は存在しません');
  } else {
    Logger.log('シート名「' + SHEET_NAME + '」が見つかりました');
  }
  
  const name = data.name;
  const count = Number(data.count);
  const unitPrice = vegetablePrices[name] || 0;
  const total = unitPrice * count;
  const date = new Date();
  const memo = data.memo;
  
  sheet.appendRow([date, name, count, unitPrice, total, memo]);
  const obj = getTotalSum();  // 合計金額取得
  sum=obj.sum;

return JSON.stringify({
    record: { 
      dateEx: "", 
      hinmei: "", 
      kingaku: 0,
      memoEx: "",
      date: date, 
      name: name, 
      count: count, 
      unitPrice: unitPrice, 
      total: total,
      memo: memo
    },
    totalSum: sum ,
    totalSumEx:0,
    balance: obj.balance
  });
}

function addRecordEx(data) {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = ss.getSheetByName(SHEET_NAME_EX);
  
  if (!sheet) {
    Logger.log('シート名「' + SHEET_NAME_EX + '」は存在しません');
  } else {
    Logger.log('シート名「' + SHEET_NAME_EX + '」が見つかりました');
  }
  
  const hinmei = data.hinmei;
  const kingaku = data.kingaku;
  const dateEx = new Date();
  const memoEx = data.memoEx;
  
  sheet.appendRow([dateEx, hinmei, kingaku, kingaku, kingaku, memoEx]);
  const obj = getTotalSum();  // 合計金額取得
  sum=obj.sumex;

return JSON.stringify({
    record: { 
      dateEx: dateEx, 
      hinmei: hinmei, 
      kingaku: kingaku,
      memoEx: memoEx,
      date: "", 
      name: "", 
      count: 0, 
      unitPrice: 0, 
      total: 0,
      memo: ""
    },
    totalSumEx: sum ,
    balance: obj.balance
  });
}

function addRecordCr(data) {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = ss.getSheetByName(SHEET_YEAR);

if (!sheet) {
    throw new Error('シート名「' + SHEET_YEAR + '」は存在しません');
  }

sheet.appendRow([
    data.cultivationRecordsDate,
    data.weather,
    data.tempMax,
    data.tempMin,
    data.precipitation,
    data.windMax,
    data.cultivationRecordsPerspective,
    data.cultivationRecordsComment,
    data.imageUrl
  ]);

return "保存しました";
}

function getTotalSum() {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = ss.getSheetByName(SHEET_NAME);
  const data = sheet.getRange(2, 5, sheet.getLastRow() - 1).getValues(); // 合計金額列
  const lastRow = sheet.getRange("A:A").getValues().filter(String).length;
  const lastValue = sheet.getRange(lastRow, 1).getValue();
  const total=data.reduce((sum, row) => sum + (Number(row[0]) || 0), 0);

const sheetEx = ss.getSheetByName(SHEET_NAME_EX);
  const dataEx = sheetEx.getRange(2, 5, sheetEx.getLastRow() - 1).getValues(); // 合計金額列
  const lastRowEx = sheetEx.getRange("A:A").getValues().filter(String).length;
  const lastValueEx = sheetEx.getRange(lastRowEx, 1).getValue();
  const totalEx=dataEx.reduce((sumex, rowex) => sumex + (Number(rowex[0]) || 0), 0);

return {sum:total,last:lastValue,sumex:totalEx  ,lastex:lastValueEx    ,balance: total-totalEx  };
 
}
  
  *******index.html
  <!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>

body {
  font-family: "Segoe UI", "Hiragino Kaku Gothic ProN", Meiryo, sans-serif;
  background-color: #f4f7f4;
}

table {
  border-collapse: separate;
  border-spacing: 20px;
  margin: 20px auto;
}

td {
  vertical-align: top;
}

.card {
  background: #ffffff;
  padding: 20px 24px;
  border-radius: 12px;
  box-shadow: 0 4px 10px rgba(0,0,0,0.08);
  min-width: 300px;
}

h2 {
  margin-top: 0;
  margin-bottom: 16px;
  color: #2f6f4e;
  border-bottom: 2px solid #e0eee6;
  padding-bottom: 6px;
}

label {
  display: block;
  margin-top: 10px;
  font-weight: 600;
  color: #444;
}

input, select, textarea {
  width: 100%;
  margin-top: 4px;
  padding: 6px 8px;
  border-radius: 6px;
  border: 1px solid #ccc;
  font-size: 14px;
}

textarea {
  resize: vertical;
}

button {
  margin-top: 14px;
  padding: 8px 16px;
  background-color: #4caf75;
  color: white;
  border: none;
  border-radius: 8px;
  font-size: 14px;
  cursor: pointer;
}

button:hover {
  background-color: #3f9a66;
}

h3 {
  margin-top: 16px;
  color: #333;
}

.total {
  font-weight: bold;
  color: #2f6f4e;
}

.balance {
  background: #fffbe6;
  padding: 16px;
  border-radius: 12px;
  box-shadow: 0 4px 10px rgba(0,0,0,0.08);
  font-size: 18px;
  font-weight: bold;
  text-align: center;
}
</style>

</head>
  <body>
    <table>
    <tr>
　　　<td>
      <h2>栽培記録</h2>
            　記録検索
        <input
         type="text"
         id="keyword"
         placeholder="キーワードをスペース区切り yyyyないと今年">
　　　　　<button onclick="search()">検索</button>
<h2>検索結果</h2>
<div id="resultSrch">
</div>
    
    
      <div id="resultCr"></div>
      <label>年月日：</label>
      <input type="date" id="CultivationRecordsDate">
    
      <label>天気：</label>
      <select id="Weather"></select> 
      <br> 
    
      <label>最高気温(℃)：</label>
      <input type="number" id="TempMax" value="0" />

<button type="button" onclick="uploadImage()">
  画像アップロード(数秒かかります)
</button>

<label>画像URL：</label>
<input type="text" id="ImageUrl" size="60">

</dt>

<td>
      <h2>畑の収穫記録</h2>
      <label>野菜：</label>
      <select id="vege"></select> 
    
      <label>個数：</label>
      <input type="number" id="count" min="1" value="1" />
      <br> 
    
      <label>メモ：</label>
      <ｔｅｘｔａｒｅａ id="memo" name="memo" rows="5" cols="40"></ｔｅｘｔａｒｅａ> 
    
      <button onclick="submitData()">収穫登録</button> 
    
      <div id="result"></div>
      <h3 id="totalSum">合計金額: -- 円</h3>
    </dt>
    <td>
      <h2>支出記録</h2>
      <label>品名:</label>
      <input type="text" id="hinmei"></select> 
    
      <label>金額：</label>
      <input type="number" id="kingaku" min="0" value="0" />
      <br> 
    
      <label>メモ：</label>
      <ｔｅｘｔａｒｅａ id="memoEx" name="memoEx" rows="5" cols="40"></ｔｅｘｔａｒｅａ> 
    
      <button onclick="submitDataEx()">支出登録</button> 
    
      <div id="resultEx"></div>
      <h3 id="totalSumEx">合計支出金額: -- 円</h3>
        <h3 id="balance">差引計算: -- 円</h3>
      <br>
    </td>
 
    </tr>
    </table><table>
 
    
    <a href='***********'>Sheet</a>
    
    <script>
      
      
      function search(){
  const keyword =
    document.getElementById("keyword").value;

google.script.run
  .withSuccessHandler(showResults)
  .withFailureHandler(showError)
  .searchRecords(keyword);

}

function showError(error){

console.error(error);

document.getElementById("resultSrch")
    .innerHTML =
      "エラー: " + error.message;
}

function showResults(results){

const div =
    document.getElementById("resultSrch");
  div.innerHTML = "";
  if(results.length === 0){
    div.innerHTML = "該当なし";
    return;
  }

results.forEach(r => {

console.log(r);

let imageHtml = "";

if(r.image && typeof r.image === "string"){

imageHtml = `
      <div>
        <img
          src="${r.image}"
          style="max-width:300px;">
      </div>
    `;
  }

const html = `
    <div class="card">

<div>${r.date}</div>
      <div>${r.comment}</div>

${imageHtml}

</div>
  `;

div.innerHTML += html;
});

}
      
      
      
      
      
      function setToday() {
  const today = new Date();

// ローカル日付を YYYY-MM-DD に
  const yyyy = today.getFullYear();
  const mm = String(today.getMonth() + 1).padStart(2, '0');
  const dd = String(today.getDate()).padStart(2, '0');

document.getElementById("CultivationRecordsDate").value =
    `${yyyy}-${mm}-${dd}`;
}

function uploadImage() {
  alert("uploadImage開始");

const file = document.getElementById("ImageFile").files[0];

if (!file) {
    alert("画像なし");
    return;
  }

const reader = new FileReader();

reader.onload = function(e) {
    alert("reader.onload");

google.script.run
      .withSuccessHandler(function(url) {
        console.log("SUCCESS");
        console.log(url);
        alert("SUCCESS: " + url);

const el =
          document.getElementById("ImageUrl");

alert("el=" + el);

el.value = url;
      })
      .withFailureHandler(function(err) {
        console.log("FAIL");
        console.log(err);
        alert("FAIL: " + err.message);
      })
      .uploadImageToDrive(
        e.target.result,
        file.name
      );
  };

reader.readAsDataURL(file);
}

document.getElementById("CultivationRecordsDate")
  .addEventListener("change", fetchWeather);

window.onload = function() {
         google.script.run.withSuccessHandler(displayResult2).init();
    };

data.forEach(function(item) {
      const opt = document.createElement("option");
      opt.value = item;
      opt.text = item;
      select.appendChild(opt);
    });

}).getWeatherOptions();
}

function fetchWeather() {
  const date = document.getElementById("CultivationRecordsDate").value;
  google.script.run
    .withSuccessHandler(function(result) {
      document.getElementById("Weather").value = result.weather;
      document.getElementById("TempMax").value = result.tempMax;
      document.getElementById("TempMin").value = result.tempMin;
      document.getElementById("Precipitation").value = result.precipitation;
      document.getElementById("WindMax").value = result.windMax;
    })
    .getWeatherData(date);
}

function submitDataCr() {
  const cultivationRecordsDate =
    document.getElementById("CultivationRecordsDate").value;
  const weather =
    document.getElementById("Weather").value;
  const tempMax =
    document.getElementById("TempMax").value;
  const tempMin =
    document.getElementById("TempMin").value;
  const precipitation =
    document.getElementById("Precipitation").value;
  const windMax =
    document.getElementById("WindMax").value;
  const cultivationRecordsPerspective =
    document.getElementById("CultivationRecordsPerspective").value;
  const cultivationRecordsComment =
    document.getElementById("memo").value;
  const imageUrl =
  document.getElementById("ImageUrl").value;

google.script.run
    .withSuccessHandler(displayResultCr)
    .addRecordCr({
      cultivationRecordsDate: cultivationRecordsDate,
      weather: weather,
      tempMax: tempMax,
      tempMin: tempMin,
      precipitation: precipitation,
      windMax: windMax,
      cultivationRecordsPerspective: cultivationRecordsPerspective,
      cultivationRecordsComment: cultivationRecordsComment,
      imageUrl:imageUrl
    });
}

function displayResult(res) {
        let response = JSON.parse(res);
        const r = response.record;

if (r.hinmei==""){
           const div = document.getElementById("result");
        const entry = document.createElement("div");
        entry.className = "result-entry";
        entry.innerHTML = `<strong>${new Date(r.date).toLocaleString()}</strong> - ${r.name} (${r.count}個 × ${r.unitPrice}円) = <strong>${r.total}円</strong><br>MEMO:<BR>${r.memo.replace(/\r?\n/g, "<br>")}`;
        //entry.innerHTML = `<strong>date</strong> - name  1個 × 100円) = <strong>200円</strong>`;
        div.appendChild(entry);
        
        document.getElementById("totalSum").innerText = `合計金額: ${response.totalSum} 円`;
        document.getElementById("balance").innerText = `差引計算: ${response.balance} 円`;

}else{
        const div = document.getElementById("resultEx");
        const entry = document.createElement("div");
        entry.className = "result-entry";
        entry.innerHTML = `<strong>${new Date(r.dateEx).toLocaleString()}</strong> - ${r.hinmei} (${r.kingaku}円</strong><br>MEMO:<BR>${r.memoEx.replace(/\r?\n/g, "<br>")}`;
       
        div.appendChild(entry);
        
        document.getElementById("totalSumEx").innerText = `合計金額: ${response.totalSumEx} 円`;

}
        
        document.getElementById("balance").innerText = `差引計算: ${response.balance} 円`;
     
      }

function displayResultCr(result) {
  document.getElementById("resultCr").innerText = result;
}
      
      loadVegetables();
      loadCultivationRecordsPerspective();
      loadWeather();
        setToday();
        fetchWeather();
        
    </script>
  </body>
</html>

※textareaのみ全角を半角にする必要あります。

※検索機能追加しました。

Myu

2026年5月13日水曜日

畑管理GASについて

0 件のコメント:

コメントを投稿

ブログアーカイブ