Our product has a concept of Folders, which are organized hierarchical, similarly to a Desktop computer Folder structure.
In the back end however, Folders are just a flat list of objects, with the hierarchical representation in the front end being derived from a “parent” Folder _id value and reference contained within each Folder object entry.
Resources can be added to Folders, just like Files are added to a Desktop computer Folder structure. In the back end, Resources are also a flat list of objects, with their parent Folder being defined from a “folder” _id value and reference to an object within the Folders table.
The requirements below are for defining standard Google Sheets queries that can be used by staff to manipulate the raw data exported, as per the example Spreadsheet.
SOURCE SAMPLE SHEET
https://www.freelancer.com/users/l.php?url=https:%2F%[login to view URL]%2Fspreadsheets%2Fd%2F1mylHys4GlC5PpDR2rnys34tcNK_vytdKS9FvLXgB4qw%2Fedit%3Fusp%3Dsharing&sig=bddbcbc556aae8376a204a1afc4dc3c0072fdc0ece21177a00a148858eef2a8e
SCENARIO
In a spreadsheet tab called Folders List, I have a flat list of all Folder objects available in the platform. Information for each Folder row is:
_id: this is the unique identifier of the Folder (Column A).
parent: this is the unique identifier of the parent Folder of the current Folder (Column B).
name: this is the front end name of the Folder (Column C).
REQUIREMENT 1 - _ID FOLDER PATH
In a new column called folder_path (Column D), generate the Folder path for each Folder object row. i.e.
Input the current Folder _id as the last value.
Determine the Parent Folder, and its own Parents, and prepend them to the current row’s Folder _id value. There can be any number of Parent Folder values.
Separate values by space and / or >.
Output example:
ParentFolder2_id > ParentFolder1_id > CurrentFolder_id
REQUIREMENT 2 - NAME FOLDER PATH
In a new column called folder_path_name (Column E), generate a user friendly version of the Folder path generated in Column D, by replacing each folder _id value by its corresponding “name” value (Column C). i.e.
ParentFolder2_id > ParentFolder1_id > CurrentFolder_idto become:
ParentFolder2_name > ParentFolder1_name > CurrentFolder_name
REQUIREMENT 3 - CROSS TABLE REFERENCE
In a separate spreadsheet tab called Resources List, I have a list of Resource objects. Information for each Resource row is:
_id: this is the unique identifier of the Resource (Column A).
folder: this is the unique identifier of the parent Folder of the current Resource (Column B).
name: this is the front end name of the Resource (Column C).
In a new column called folder_path_name (Column D), populate for each Resource row object:
The folder_path_name value defined in the Folder List tab (Column D) that corresponds to the “folder” value _id of the Resource as defined in Column B, and matched in the Folder List tab _id column (Column A).
REQUIREMENT 4 - CROSS TABLE REFERENCE
For each Folder row in the Folders List table, in a new column called resource_count (Column E), generate a sum of the number of rows in Resources List table when “folder” _id (Column A) is the same as the current Folder row _id (Column A)
when you apply, please let me know what language you will use.
Hi there,
I am an expert in Google App Script with more than two years of experience. I can deliver a function that handles all the scenarios described in the project description. The algorithms will be designed to guarantee linear time complexity to reduce runtime resources. Contact me in chat and I can get started right away. Thanks for your time and consideration.
I have developed a number of google sheet applications in my work place.
I have been using / developing google sheet applications for 7 years now.
I am confident of providing you the best solution.
✨ Hello ✨
I am interested in your offer and I am ready to cooperate.
I want to write a function to populate your google sheet with the desired values.
English