Searching in Google Drive to locate a specific folder and file and then inserting a link

Question

Searching in Google Drive to locate a specific folder and file and then inserting a link

I am looking for a way to search and locate specific text within my index spreadsheet that is related to the values in columns A and B. These values are used to create product folders and Google sheets based on a naming convention.

The product folder is named using the formula:

FolderName = ref + ' - ' + location

, which is stored inside a main parent folder. Within this product folder, a Google sheet is created with the name:

newDocName = ref + ' [Descriptions] - ' + location

In the index spreadsheet, I want to automatically populate column L & M with links to the corresponding product folder and sheet file. Is it possible to achieve this directly within the spreadsheet or would I need to use Google Apps Script with an On Open trigger?

If utilizing Google Apps Script is necessary, how can I implement a script to search for and fill in the correct folder and file links in columns L & M? Any guidance would be appreciated.

Thank you.

https://i.sstatic.net/6JoqI.png

javascript google-apps-script google-sheets google-sheets-formula

Answer 1

Answer №1

Problem Statement:

If my understanding is correct:

You have a folder in Google Drive that contains various child folders and files, with titles based on values from columns A and B following specific conventions.
Your goal is to retrieve the URLs of these folders and files linked to each row in the spreadsheet and save them in columns L and M respectively.

Solution Approach:

To address this issue, create an Apps Script function that performs the following steps:

Locate the parent folder using DriveApp.getFoldersByName.
Extract data from columns A and B using getValues.
Iterate through the retrieved values, obtaining the respective folder and file names based on the defined convention for each row.
Determine the folder name by using Folder.getFoldersByName.
Find the file name via getFilesByName.
Update the URLs in columns L:M using setValues.

Sample Code Snippet:

const SHEET_NAME = "Sheet1"; // Insert your sheet name
const PARENT_FOLDER_ID = "FOLDER_ID"; // Include 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);
}

Answer 2

Problem Statement:

If my understanding is correct:

You have a folder in Google Drive that contains various child folders and files, with titles based on values from columns A and B following specific conventions.
Your goal is to retrieve the URLs of these folders and files linked to each row in the spreadsheet and save them in columns L and M respectively.

Solution Approach:

To address this issue, create an Apps Script function that performs the following steps:

Locate the parent folder using DriveApp.getFoldersByName.
Extract data from columns A and B using getValues.
Iterate through the retrieved values, obtaining the respective folder and file names based on the defined convention for each row.
Determine the folder name by using Folder.getFoldersByName.
Find the file name via getFilesByName.
Update the URLs in columns L:M using setValues.

Sample Code Snippet:

const SHEET_NAME = "Sheet1"; // Insert your sheet name
const PARENT_FOLDER_ID = "FOLDER_ID"; // Include 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);
}

Answer 3

Answer №2

If you're looking to achieve your goal, the Drive API is a great choice. You can activate the Drive API in your script and execute the Drive.Files.list() method with a search query. Here's an example:

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

For more details and additional options, check out the search query guide.

Answer 4

If you're looking to achieve your goal, the Drive API is a great choice. You can activate the Drive API in your script and execute the Drive.Files.list() method with a search query. Here's an example:

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

For more details and additional options, check out the search query guide.

Searching in Google Drive to locate a specific folder and file and then inserting a link

Answer №1

Problem Statement:

Solution Approach:

Sample Code Snippet:

Answer №2

Similar questions

Learn the easy way to automatically generate HTML in a Vue template file

Embarking on your journey with the Withings API

Intersecting realms document

Disabling the experimental app directory feature in NextJs

Changing color based on AngularJS condition using CSS

i18Next: utilize specific locales for certain languages and combine language with location for all others

Local AngularJS template fails to load, yet loads successfully on Plnkr platform

Extend the start day by one day for the checkout option on the Pikaday datepicker

Having issues with Cypress testing of Material-UI datepicker on Github actions

Issues with resetting AngularJS form---"The AngularJS form

Convert components into <input> using the "Edit" button, and store information with the "Save" button

Angular Datepicker MinDate function prevents selection of dates "behind" by one

"Populate a div with an array of images extracted from a JSON file

Unable to configure raycaster layers within Three.js framework

When using the CDN import version, React does not render components or code

Manipulator - Unveiling Discord User Profile Popup

Angular animations are failing to function in the Visual Studio 2017 template

Using jQuery and regex to only allow alphanumeric characters, excluding symbols and spaces

What is the most efficient method for swapping out a portion of an array with a different one in Javascript?

Difficulty encountered when attempting to modify textures in three.js