I have an index spreadsheet, and not sure how to search and find Folder and File containing specific text related to values in column A and B.

In this spreadsheet, I create a product folder based on values from column A & B - FolderName = ref ' - ' location and store this inside one main parent folder.

Inside this new product folder, I then create a Google sheet with similar naming convention - newDocName = ref ' [Descriptions] - ' location

ref = value from column A location = value from column B

'- Parent Folder '-- Product Folder '--- Product Sheet File

However, inside the index spreadsheet, I would like to auto-fill column L & M with the link to the new Folder & File that relates to that product.

Column L = Folder Link (Product Folder) Column M = Sheet Link (Product Sheet File)

Can this be done inside the index spreadsheet, or would it need to be done with Google app script with an On Open trigger? If so, How can I use Google app script to search and fill in the relevant Folder & File GD Link to column L & M?

Thanks.

CodePudding user response：

I think for your purposes Drive API does exactly what you want. Enable Drive API in your script and run Drive.Files.list() method with search query. Something like that:

Drive.Files.list({q: "fullText contains 'Example One'"});

More info and options on search query

CodePudding user response：

Issue:

If I understand correctly:

• A folder in your Drive contains a series of folders and files (inside the different child folders) whose titles depend on the values of columns A and B, according to the described conventions.
• You want to get the URL of the folders and files corresponding to each sheet row and write these to columns L and M, respectively.

Solution:

Create an Apps Script function to do the following:

• Get the parent folder via DriveApp.getFoldersByName.
• Get the values from columns A and B, using getValues.
• Iterate through the resulting values. For each row, retrieve the corresponding folderName and sheetName according to convention.
• Get the product folder name via Folder.getFoldersByName.
• Get the file name via getFilesByName.
• Write the URLs to L:M via setValues.

Code sample:

const SHEET_NAME = "Sheet1"; // Add your sheet name
const PARENT_FOLDER_ID = "FOLDER_ID"; // Add your parent folder id
function writeUrls() {
  const parentFolder = DriveApp.getFolderById(PARENT_FOLDER_ID);
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const lastRow = sheet.getLastRow();
  const values = sheet.getRange("A2:B"   lastRow).getValues();
  const urls = values.map(([ref, location]) => {
    const folderName = `${ref} - ${location}`;
    const sheetName = `${ref} [Descriptions] - ${location}`;
    const foldersIter = parentFolder.getFoldersByName(folderName);
    let rowUrls = new Array(2);
    if (foldersIter.hasNext()) {
      const folder = foldersIter.next();
      rowUrls[0] = folder.getUrl();
      const filesIter = folder.getFilesByName(sheetName);
      if (filesIter.hasNext()) rowUrls[1] = filesIter.next().getUrl();
    }
    return rowUrls;
  });
  sheet.getRange("L2:M"   lastRow).setValues(urls);
}