M
M
mUchenik2021-01-22 14:41:08
Google Sheets
mUchenik, 2021-01-22 14:41:08

How to set up data pull?

Hi all!
Please tell me if it is possible to implement such a thing:
There is a folder "Main"
There is a folder "With subfolders"
In the folder "Main" there is a file "All subfolders"
The file contains a list of subfolders (preferably with creation dates + links to the subfolder)
The idea is as follows:
If I create a subfolder in the folder "With subfolders", then the name of the subfolder + date of creation + link to the subfolder is automatically written to the file "All subfolders".

Well, from the opposite:
If I write a name in the "All subfolders" file in a free cell in order, then a subfolder with the corresponding name appears in the "With subfolders" folder.

I understand that sounds like nonsense,
Is it realistic to implement this?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Aleksei Shebanits, 2021-01-26
@shebanits

1. Create a simple table
600ff0b05eb84698106396.png
2. Copy your main folder variable into the ID variable.
3. Use the downloadData() function to load subfolder data into the table.
4. To create a folder, write the name of the folder in the folder name column and run the createFolder() function.
600ff1aaea441717717040.png

let ID = "<id вашей папки>";

function createFolder() {
  let mainFolder = DriveApp.getFolderById(ID);
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let foldersNames = sheet.getRange(2, 1, sheet.getLastRow(), 3).getValues();
  let folders = mainFolder.getFolders();
  let arr = [];

  while (folders.hasNext()) {
    let folder = folders.next();
    arr.push({
      "name": folder.getName(),
      "folderID": folder.getId(),
      "lastUpdated": folder.getLastUpdated()
    })
  }

  let res = arr.map(item => item.name);

  foldersNames.forEach((el, index) => {
    if (typeof el[0] === "string" && res.indexOf(el[0]) === -1 && el[0] != "") {
      let nFolder = mainFolder.createFolder(el[0]);
      sheet.getRange(index + 2, 2, 1, 2).setValues();
console.log(`New folder created! 
name: ${el[0]}
creation date: ${nFolder.getLastUpdated()}
`)
    }

  })

}

function downloadData() {
  let mainFolder = DriveApp.getFolderById(ID);
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let foldersNames = sheet.getRange(2, 1, sheet.getLastRow(), 3).getValues().map(item => item[0]);
  let folders = mainFolder.getFolders();
  let arr = [];

  while (folders.hasNext()) {
    let folder = folders.next();
    arr.push({
      "name": folder.getName(),
      "folderID": folder.getId(),
      "lastUpdated": folder.getLastUpdated()
    })
  }

  arr.forEach(el => {
    if (foldersNames.indexOf(el.name) === -1 && foldersNames.indexOf(el.folderID) === -1) {
      sheet.getRange(sheet.getLastRow() + 1, 1, 1, 3).setValues();
    }
  })
  console.log(arr)
}

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question