自分用メモ

めも１

function myFunction() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
var sht = spreadsheet.getSheetByName("シート4");
for(var i = 1; i <= 10; i++) {
sheet.getRange(1+i,1).setDataValidation(SpreadsheetApp.newDataValidation()
.setAllowInvalid(true)
.requireValueInRange(sht.getRange(i,i,5,1), true)
.build());
};
};

日付

function rinku() {
var sheet = SpreadsheetApp.getActive();
var sh = sheet.getActiveSheet();
var last_row = sheet.getLastRow();
var ff = sh.getRange(1,2).getValue()* 86400000;
for(var i=1;i<=last_row ;i++){
var hh = sh.getRange(i,1).getValue()* 86400000;
if (hh == ff){
sh.getRange(i,3).setValue("test");
}
}
}

集計

function test() {

var result = Browser.msgBox("かくにん", "いいですか", Browser.Buttons.OK_CANCEL);
if(result == "ok"){

var cptoss = SpreadsheetApp.getActiveSpreadsheet();
var cptosp = cptoss.getSheetByName('シート15'); //管理用シート
var cptosh = cptoss.getSheetByName('シート14'); //サマリーシート

var clearRow = cptosh.getLastRow();
var clearRange = cptosh.getRange(4,2,clearRow,4);
clearRange.clearContent();

for (var i = 1; i <=cptosp.getLastRow() ; i++) {
var user = cptosp.getRange(i,1,1,1).getValues();
var cpfromid = SpreadsheetApp.openById(user); //コピー元ファイルID
var cpfromsh= cpfromid.getSheetByName('シート14'); //コピー元シート名

var cpfromRow = cpfromsh.getLastRow()+1;
var cptoRow = cptosh.getLastRow()+1;
var copyValue = cpfromsh.getRange(5,2,cpfromRow,4).getValues();

cptosh.getRange(cptoRow,2,cpfromRow,4).setValues(copyValue);
}

var sortshRow = cptosh.getLastRow();
var sortrange = cptosh.getRange(4,2,sortshRow,4);
sortrange.sort([{column: 2, ascending: true}, {column: 3, ascending: true}]);
}
else {
Browser.msgBox("はい")
}
}

#削除
function delete_duplication(){
var ss = SpreadsheetApp.openById('1LC0BNbcnMzcptjjGC6DHzEEV7ffozy0jOhoL-VF6Da4');
var sh = ss.getSheetByName('シート14');
var last_row = sh.getLastRow();
for(var i = 2; i < last_row; i++){
var range = sh.getRange("C"+ i);
var value = range.getDisplayValue();
if(value != "りんご"){
var start_row = i;
var num_row = 1;
sh.deleteRows(start_row, num_row);
i = i - 1;
}
}
}

#取得
function getFileListInFolder() {
var folder_id = '1NlvxB18IYC0qHldhZ1-hfrJYc-lh6OG4';
var url = 'https://drive.google.com/drive/folders/' + folder_id, // URL of Google Drive folder.
paths = url.split('/'), // Separate URL into an array of strings by separating the string into substrings.
folderId = paths[paths.length - 1], // Get a last element of paths array.
folder = DriveApp.getFolderById(folderId),
files = folder.getFiles(),
list = [],
rowIndex = 1, // The starting row of a range.
colIndex = 1, // The starting row of a column.
ss, sheet,range,
sheetName = 'シート1';

// Creating a data array from a files iterator.
// Note: Latest file is the first.
while(files.hasNext()) {
var buff = files.next();
list.push([buff.getName(), buff.getUrl()]);
};

// folder.getFiles()では、ファイルは新しいものが最初に来ます
// 登録された順にしたいので、reverse() で調整します
list = list.reverse()

ss = SpreadsheetApp.getActive();
sheet = ss.getSheetByName(sheetName);
range = sheet.getRange(rowIndex, colIndex, list.length, list[0].length);

// 対象の範囲にまとめて書き出します
range.setValues(list);
}

#1%7C2%7C3%7C4%7C5%7C6%7C7%7C8%7C9%7C10%7C11%7C12%7C13
dj00aiZpPWRKZ1BDYzZvQ1lDaiZzPWNvbnN1bWVyc2VjcmV0Jng9MmI-
uGzf9LQqNmWPzPwSZHI039bQuqvPE6qkZXkmFgnk