let folderName = "myCertifyName";

let newFilesName = "รูปของนักเรียน no";

let ourSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

let roughSheet = ourSpreadsheet.getSheetByName("ชีต1");

//let dataSheet = ourSpreadsheet.getSheetByName("data");

let sourceRange = roughSheet.getDataRange() // sourceSheet.getDataRange();

function onOpen() {

var ui = SpreadsheetApp.getUi();

// Or DocumentApp or FormApp.

ui.createMenu('Custom Menu')

.addItem('แสดงชื่อไฟล์ทั้งโฟลดเดอร์', 'fetchFileNamesFunction')

.addSeparator()

.addItem('เปลี่ยนชื่อไฟล์ทั้งหมด', 'renameFilesFunction')

.addToUi();

}

function fetchFileNamesFunction() {

var folders = DriveApp.getFoldersByName(folderName);

var folder = folders.next();

var myFileIterator = folder.getFiles();

var fileNumber = 1;

while (myFileIterator.hasNext()) {

var currentFile = myFileIterator.next();

var currentFileName = currentFile.getName();

//roughSheet.getRange(2, 1);

roughSheet.appendRow([

fileNumber,

currentFileName]);

fileNumber++

}

}

/*

function copyRowsWithCopyTo() {

//sourceRange.copyTo(dataSheet.getRange(1, 1));

}

*/

function renameFilesFunction() {

var folders = DriveApp.getFoldersByName(folderName);

var folder = folders.next();

//var lastRow = dataSheet.getLastRow();

var lastRow = roughSheet.getLastRow();

var oldNamesArr = [];

var newNamesArr = [];

var columnOfOldNames = 2;

var columnOfNewNames = 1;

oldNamesArr = roughSheet.getRange(1, columnOfOldNames, lastRow).getValues();

Logger.log(oldNamesArr)

newNamesArr = roughSheet.getRange(1, columnOfNewNames, lastRow).getValues();

for (let i = 0; i < oldNamesArr.length; i++) {

var myFileIterator = folder.getFilesByName(oldNamesArr[i]);

while (myFileIterator.hasNext()) {

var currentFile = myFileIterator.next();

currentFile.setName(newFilesName + newNamesArr[i]);

}

}

}