自分用メモ

# めも１

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