回答編集履歴

2019/05/22 12:59

投稿

スコア12705

answer CHANGED Viewed

@@ -3,7 +3,7 @@
     const dest = SpreadsheetApp.openById('コピー先ID');
     const sheetName = 'シート一覧';
     const folderName = '翻訳元_未コピー';
-    pickSpreadsheetInFolderNamed(folderName).forEach(function (e) { copyAll(dest, e); });
+    pickSpreadsheetInFolderNamed(folderName).forEach(function (e) { copyAll(dest, SpreadsheetApp.open(e)); });
     resetSheetIndexSheet(dest, sheetName);
 }
 function pickSpreadsheetInFolderNamed(name) {

あ

2019/05/22 12:59

投稿

papinianus

スコア12705

answer CHANGED Viewed

@@ -33,7 +33,8 @@
 function resetSheetIndexSheet(spreadsheet, sheetName) {
     const sheet = upsertSheet(spreadsheet, sheetName);
     sheet.clear();
-    const dat = spreadsheet.getSheets().map(function (e) { return [e.getSheetName(), e.getSheetId()]; }).filter(function (e) { return e[0] !== sheetName; });
+    //const dat = spreadsheet.getSheets().map(function (e) { return [e.getSheetName(), e.getSheetId()]; }).filter(function (e) { return e[0] !== sheetName; });
+    const dat = spreadsheet.getSheets().map(function (e) { return [e.getSheetName(),]; }).filter(function (e) { return e[0] !== sheetName; });
-    sheet.getRange(1, 1, dat.length, 2).setValues(dat);
+    sheet.getRange(1, 1, dat.length, dat[0].length).setValues(dat);
 }
 ```

あ

2019/05/21 04:19

投稿

papinianus

スコア12705

answer CHANGED Viewed

@@ -1,19 +1,39 @@
 ```javascript
-function q189933() {
+function procedure() {
-  const destination = SpreadsheetApp.openById('コピー先ID');
+    const dest = SpreadsheetApp.openById('コピー先ID');
+    const sheetName = 'シート一覧';
+    const folderName = '翻訳元_未コピー';
+    pickSpreadsheetInFolderNamed(folderName).forEach(function (e) { copyAll(dest, e); });
+    resetSheetIndexSheet(dest, sheetName);
+}
+function pickSpreadsheetInFolderNamed(name) {
+    var files = [];
+    const folderIter = DriveApp.getFoldersByName(name);
+    while (folderIter.hasNext()) {
+        var folder = folderIter.next();
-  SpreadsheetApp.openById('元のファイルID').getSheets().forEach(
+        var fileIter = folder.getFilesByType('application/vnd.google-apps.spreadsheet');
-    function(e) {
+        while (fileIter.hasNext()) {
-      e.copyTo(destination);
+            files.push(fileIter.next());
+        }
     }
-  );
+    return files;
 }
+function copyAll(destSpreadsheet, sourceSpreadsheet) {
+    sourceSpreadsheet.getSheets().forEach(
-function q189933_() {
+        function (e) {
-  SpreadsheetApp.openById('コピー先ID').insertSheet('Sheet List', 0);
+            e.copyTo(destSpreadsheet).setName(e.getName());
+        }
+    );
 }
-function q189933__() {
+function upsertSheet(spreadsheet, sheetName) {
-  const sheet = SpreadsheetApp.openById('コピー先ID').getSheetByName('Sheet List');
+    const sheet = spreadsheet.getSheetByName(sheetName);
-  sheet.clear();
+    if (sheet !== null) { return sheet; }
-  const dat = SpreadsheetApp.openById('コピー先ID').getSheets().map(function(e){return [e.getSheetName(), e.getSheetId()];}).filter(function(e){return e[0] !== 'Sheet List';});
+    return spreadsheet.insertSheet(sheetName, 0); //0が挿入位置を示しており、先頭。移動の必要はないと考えた(https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#insertSheet(String,Integer))
-  sheet.getRange(1, 1, dat.length, 2).setValues(dat);
 }
+function resetSheetIndexSheet(spreadsheet, sheetName) {
+    const sheet = upsertSheet(spreadsheet, sheetName);
+    sheet.clear();
+    const dat = spreadsheet.getSheets().map(function (e) { return [e.getSheetName(), e.getSheetId()]; }).filter(function (e) { return e[0] !== sheetName; });
+    sheet.getRange(1, 1, dat.length, 2).setValues(dat);
+}
 ```