Skip to content

Instantly share code, notes, and snippets.

@hokorobi
Last active October 22, 2017 06:12
Show Gist options
  • Save hokorobi/8a45192252577134521511e657fc0bd1 to your computer and use it in GitHub Desktop.
Save hokorobi/8a45192252577134521511e657fc0bd1 to your computer and use it in GitHub Desktop.
GAS Spreadsheet
function executePerDay() {
inputFormula();
copyTweets();
inputDate();
}
function inputDate() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
//var lastRow = sheet.getLastRow();
var d = new Date();
var year = d.getFullYear();
var month = d.getMonth() + 1;
var day = d.getDate();
var youbi = d.getDay();
var youbis = ['日', '月', '火', '水', '木', '金', '土'];
sheet.appendRow(["", "Sep"]);
sheet.appendRow(["", "", year + "-" + month + "-" + day + " (" + youbis[youbi] + ")"]);
sheet.appendRow(["", "Sep"]);
sheet.appendRow(["", "", "* tweet [Diary]:"]);
sheet.appendRow(["", "Sep"]);
}
function inputFormula() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var lastRow = sheet.getLastRow();
var range;
for (var i = 1; i <= lastRow; i++) {
range = sheet.getRange(i, 1);
if (range.getValue() == "") {
continue;
}
range = sheet.getRange(i, 3);
if (range.getValue() == "") {
range.setValue("=text(value((REGEXEXTRACT(A" + i + ",\"[^ ]+$\"))),\"HH:mm \") & B" + i);
// range.setValue("=text(value((REGEXEXTRACT(indirect(\"R" + i + "C1\", false),\"[^ ]+$\"))),\"HH:mm \") & indirect(\"R" + i + "C2\", false)")
}
}
}
function copyTweets() {
var fromSheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var lastRow = fromSheet.getLastRow();
var fromRange = fromSheet.getRange(1, 3, lastRow);
var values = fromRange.getValues();
var toSheet = SpreadsheetApp.getActive().getSheetByName('Sheet2');
var toLastRow = toSheet.getLastRow();
var toRange = toSheet.getRange(toLastRow + 2, 1, lastRow);
toRange.setValues(values);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment