Nature remoで部屋の温度と湿度を見える化。そこから不快指数を計算し、外気のそれと比較して窓を開けるべきか判断するまで。(2)

Rawデータ用シートと編集用シート

=IMPORTRANGE(“XXXXX”,”YYYYY!x:y”)XXXXX:参照したいブックのアドレスを指定
YYYYY:XXXXXで指定したブックのうち、参照するシート名の指定
x:y : 参照したいセルの範囲

Google app scriptでデータが追加されるたびに関数を入力する

・Google spread sheetの編集はここまでで、Google app scriptを編集していく。Google app scriptの編集方法は前回参照。

//nature-remo-monitor_copyのシートidを指定
var book = SpreadsheetApp.openById(‘XXXXXXXXXXXXXXXXXXXXXXXXXX’)
//Sheet1を指定
var sheet = book.getSheetByName(‘Sheet1’)
 const Avalues = sheet.getRange(‘A:A’).getValues();
const Qvalues = sheet.getRange(‘Q:Q’).getValues();
const Alastrow = Avalues.filter(String).length;
const Qlastrow = Qvalues.filter(String).length;
for (var i = Qlastrow+1;i<= Alastrow; i++){
var Qstrformula = “=IF(AND(DATE(1900,E” + i + “,F” + i + “)>DATE(1900,MONTH(TODAY()),DAY(TODAY())-60),DATE(1900,E” + i + “,F” + i + “)<DATE(1900,MONTH(TODAY()),DAY(TODAY())+30)),” + ‘“-2~+1MONTH”’ +”,” +’”FALSE”’ +”)”
var Rstrformula = “=IF(DATE(B” + i + “,E” + i + “,F” + i + “)=Today(),”+’”TODAY”’+”,IF(AND(DATE(B” + i + “,E” + i + “,F” + i + “)>=Today()-7,DATE(B” + i + “,E” + i + “,F” + i + “)<Today()),”+’”1_WEEK”’+”,IF(AND(DATE(B” + i + “,E” + i + “,F” + i + “)>=Today()-30,DATE(B” + i + “,E” + i + “,F” + i + “)<Today()),”+’”1_MONTH”’+”,IF(AND(DATE(B” + i + “+1,E” + i + “,F” + i + “)>=Today()-15,DATE(B” + i + “+1,E” + i + “,F” + i + “)<Today()+15),”+’”1YEAR_AGO_THIS_30DAYS”’+”,” + ‘“ELSE”’+”))))”
j = i+1
// var Sstrformula = “=IF(OR(ISBLANK(A” + j + “),ISBLANK(S” + j + “)),”+ ‘“UPTODATE”’+”,” + ‘“OLD”’ + “)”
var Sbeforestr = “OLD”
var Sstr = “UPTODATE”
var Tstrformula = “=ROUND(N” + i +”*0.81 + J” + i +”*0.01*(N” + i +”*0.99–14.3)+46.3,1)”
var Ustrformula = “=37-((37-N” + i +”)/(0.68–0.0014*J” + i +”+(1/1.76)))-0.29*N” + i +”*(1-J” + i +”/100)”
sheet.getRange(i, 17).setFormula(Qstrformula)
sheet.getRange(i, 18).setFormula(Rstrformula)
// sheet.getRange(i, 19).setFormula(Sstrformula)
sheet.getRange(i-1, 19).setValue(Sbeforestr)
sheet.getRange(i, 19).setValue(Sstr)
sheet.getRange(i, 20).setFormula(Tstrformula)
sheet.getRange(i, 21).setFormula(Ustrformula)
}
function myFunction() {
//nature-remo-monitor_copyのシートidを指定
var book = SpreadsheetApp.openById(‘XXXXXXXXXXXXXXXXXXXXXXXXXX’)
//Sheet1を指定
var sheet = book.getSheetByName(‘Sheet1’)

const Avalues = sheet.getRange(‘A:A’).getValues();
const Qvalues = sheet.getRange(‘Q:Q’).getValues();
const Alastrow = Avalues.filter(String).length;
const Qlastrow = Qvalues.filter(String).length;

for (var i = Qlastrow+1;i<= Alastrow; i++){
var Qstrformula = “=IF(AND(DATE(1900,E” + i + “,F” + i + “)>DATE(1900,MONTH(TODAY()),DAY(TODAY())-60),DATE(1900,E” + i + “,F” + i + “)<DATE(1900,MONTH(TODAY()),DAY(TODAY())+30)),” + ‘“-2~+1MONTH”’ +”,” +’”FALSE”’ +”)”
var Rstrformula = “=IF(DATE(B” + i + “,E” + i + “,F” + i + “)=Today(),”+’”TODAY”’+”,IF(AND(DATE(B” + i + “,E” + i + “,F” + i + “)>=Today()-7,DATE(B” + i + “,E” + i + “,F” + i + “)<Today()),”+’”1_WEEK”’+”,IF(AND(DATE(B” + i + “,E” + i + “,F” + i + “)>=Today()-30,DATE(B” + i + “,E” + i + “,F” + i + “)<Today()),”+’”1_MONTH”’+”,IF(AND(DATE(B” + i + “+1,E” + i + “,F” + i + “)>=Today()-15,DATE(B” + i + “+1,E” + i + “,F” + i + “)<Today()+15),”+’”1YEAR_AGO_THIS_30DAYS”’+”,” + ‘“ELSE”’+”))))”
j = i+1
// var Sstrformula = “=IF(OR(ISBLANK(A” + j + “),ISBLANK(S” + j + “)),”+ ‘“UPTODATE”’+”,” + ‘“OLD”’ + “)”
var Sbeforestr = “OLD”
var Sstr = “UPTODATE”
var Tstrformula = “=ROUND(N” + i +”*0.81 + J” + i +”*0.01*(N” + i +”*0.99–14.3)+46.3,1)”
var Ustrformula = “=37-((37-N” + i +”)/(0.68–0.0014*J” + i +”+(1/1.76)))-0.29*N” + i +”*(1-J” + i +”/100)”
sheet.getRange(i, 17).setFormula(Qstrformula)
sheet.getRange(i, 18).setFormula(Rstrformula)
// sheet.getRange(i, 19).setFormula(Sstrformula)
sheet.getRange(i-1, 19).setValue(Sbeforestr)
sheet.getRange(i, 19).setValue(Sstr)
sheet.getRange(i, 20).setFormula(Tstrformula)
sheet.getRange(i, 21).setFormula(Ustrformula)
}
}

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store