gas覚え書き

function createFolderList() {
  var folderList = [[0, 'aaa/bbb/ddd'],
                    [1, 'aaa/bbb/eee'],
                    [2, 'aaa/ccc/fff/ggg']];
  var parentFolderId = "xxxx";
  var srcFileId = "yyy";

  for(var j = 0; j<folderList.length; j++){
    var editFileId = copyFile(srcFileId, parentFolderId, folderList[j][1]);
    if(editFileId!=""){
      editFile(folderList[j][0], editFileId);
    }
  }
}

function editFile(editType, fileId)
{
  var ss = SpreadsheetApp.openById(fileId);
  var sheet = ss.getSheets()[0];  // first sheet
  var ranges = sheet.getRange(1, 1, 10, 10);
  var values = ranges.getValues();

  if(editType == 0)
  {
    values[1][1] = "aaa";
  }
  ranges.setValues(values);
}

function copyFile(fileId,folderId, destPath) {
  var targetFolder = DriveApp.getFolderById(folderId);
  var folders = destPath.split('/');

  for (var i = 0; i < folders.length; i++) {
    var folderName = folders[i];
    var childFolder = targetFolder.getFoldersByName(folderName);

    if (childFolder.hasNext()) {
      targetFolder = childFolder.next();
    } else {
      targetFolder = targetFolder.createFolder(folderName);
    }
  }

  var ss = SpreadsheetApp.openById(fileId);
  var fileName = ss.getName();
  var files = targetFolder.getFilesByName(fileName);

  if (!files.hasNext()) {
    var sourceFile = DriveApp.getFileById(fileId);
    sourceFile.makeCopy(sourceFile.getName(), targetFolder);

    return sourceFile.getId();
  }

  return "";
}

function renameFile() {
  var newName = 'test_file_rename';
  var file = DriveApp.getFileById("xxx");
  file.setName(newName);
}

function myFunc()
{
  const replace_table =[
    ["りんご", "Apple"],
    ["みかん", "Orange"],
    ["ばなな", "Banana"],
    ["犬", "Dog"],
    ["猫", "Cat"],
  ];

  const replace_table2 = [
    ["赤星","Red Start"],
    ["藤森","Fuji Mori"],
    ["大山","Oyama"],
    ["佐藤","Sato"],
    ["木浪","Kinami"],
  ];

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const currentSheet = spreadsheet.getActiveSheet();
  var ranges = currentSheet.getDataRange();
//  var ranges = currentSheet.getRange(1,7,20,1);
  var values = ranges.getValues();

  values = replaceString(replace_table, values);
  values = replaceString(replace_table2, values);

//  ranges.setValues(values);
  currentSheet.getDataRange().setValues(values);
}

function replaceString(replace_table, values)
{
  for(var i=0;i<values.length;i++)
  {
    for(var j=0;j<values[0].length;j++)
    {
      if(values[i][j] == "")  continue;

      var temp = "";
      for(var k=0;k<replace_table.length;k++)
      {
        if(!values[i][j].match(replace_table[k][0])) continue;

        temp = values[i][j].replace(replace_table[k][0], replace_table[k][1]);
      }

      if(temp!="")
      {
        var temp2 = LanguageApp.translate(temp, "ja", "en");
        values[i][j] = temp + "\n\n" + temp2;
        Utilities.sleep(100);
      }
    }
  }
  return values;
}

function insertHyperlink() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("test");
  var range = sheet.getRange("B1");
  var url = "https://example.com";
  var text = "aaa";

  range.setFormula('=HYPERLINK("' + url + '", "' + text + '")');
}

function insertLinkAndText() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("aaa");
  var range = sheet.getRange("A1");
  var url = "https://example.com";
  var textBeforeLink = "111";
  var textAfterLink = "222";

  var richText = SpreadsheetApp.newRichTextValue()
    .setText(textBeforeLink + url + textAfterLink)
    .setLinkUrl(textBeforeLink.length, textBeforeLink.length + url.length, url)
    .build();

  range.setRichTextValue(richText);
}

function insertMultipleLinks() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("test");
  var range = sheet.getRange("A1"); // 読み込みたいセル範囲に変更
  var cellValue = range.getValue();

  var msg = "link1, link2";
  var RichTextValue = SpreadsheetApp.newRichTextValue()
  .setText(msg)
  .setLinkUrl(0, 5, "https://example1.com")
  .setLinkUrl(7, 12, "https://example2.com")
  .build();
  sheet.getRange("A4").setRichTextValue(RichTextValue);
}