Google Sheets - Script required to change name of files

I'm looking for a way to rename files at a specific time of day, maybe 1am but this can be done with triggers which will need configuring.

The files in Google Drive will be renamed from data in a Google Sheet - see below as an example.

Screenshot of example data

I'm hoping a script will be able to reference the URL of the file in Column F (this maybe a different column when i come to make this live) and somehow change the name of the file based on a lookup range referencing the columns of data, bringing together the data in column D and E into the file name (or the referenced columns).

Referencing the old file name is not possible as each file in Google Drive will be different.

Is anyone able to help?

I've tried the following but i have a feeling that this wrong

const SPREADSHEET_ID = "SPREADSHEET_ID"; // Change according to your preferences
const SHEET_NAME = "Sheet1"; // Change according to your preferences
const FOLDER_ID = "DRIVE_FOLDER_ID"; // Change according to your preferences

function changeFolderNames() {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = ss.getSheetByName(SHEET_NAME);
  const folder = DriveApp.getFolderById(FOLDER_ID);
  const values = sheet.getRange("A2:B" + sheet.getLastRow()).getValues(); // If there's no header row, use "A:B" instead
  values.forEach(row => {
    const [oldName, newName] = row;
    const fileIter = folder.getFilesByName(oldName);
    while (fileIter.hasNext()) {
      const file = fileIter.next();
      file.setName(newName);
    }
  });
}

I believe your goal is as follows.

• You want to retrieve the values from columns "D" to "F".

• Column "F" has the URLs of files. From your showing image, the format of all URLs is only https://drive.google.com/open?id={fileId}.

• You want to rename the file of file URL using the values of columns "D" and "E". The new filename is as follows.

  > If column D says Name and Column E says Folder, i want the file name to be changed to "Name Folder.pdf" or "Name Folder.jpg".

Modification points:

• In your script, it seems that the values are retrieved from columns "A" and "B". But, it seems that you want to retrieve the values from columns "D" to "F".
• In your script, it seems that the URL is not used. The filename is used. I thought that this might not be your expected result.

When these points are reflected in your script, how about the following modification?

Modified script:

Before you use this script, please set your Spreadsheet ID and sheet name.

function changeFolderNames() {
  const SPREADSHEET_ID = "###"; // Change according to your preferences
  const SHEET_NAME = "Sheet1"; // Change according to your preferences

  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = ss.getSheetByName(SHEET_NAME);
  const values = sheet.getRange("D2:F" + sheet.getLastRow()).getValues();
  values.forEach(([d, e, f]) => {
    const fileId = f.match(/id\=(.*?)(&|$)/);
    if (fileId) {
      try {
        const file = DriveApp.getFileById(fileId[1]);
        const ext = file.getName().split(".");
        const extension = ext.length > 1 ? `.${ext.pop()}` : "";
        const newName = `${d} ${e}${extension}`; // From "i want the file name to be changed to "Name Folder.pdf" or "Name Folder.jpg"".
        file.setName(newName);
      } catch (e) {
        console.log(e.message); // If an error occurs, you can see it in the log.
      }
    } else {
      console.log(`File ID couldn't retrieved from "${f}".`);
    }
  });
}

• When this script is run, the filenames of URLs of column "F" are changed by the values of columns "D" and "E".

Note:

• In this sample script, the new filename is const newName = ${d} ${e}${extension};. If you want to change this, please modify it.

• From your showing sample image, I believe the format of all URLs is only https://drive.google.com/open?id={fileId}. But, if another format is used, this script cannot be used. Please be careful about this. In that case, the error message is shown in the log.

• If the file couldn't be retrieved and the filename cannot be changed, you can see the error message in the log. In that case, please check the permission and the file ID, again.