Google Apps Script: Creative Task

Loo projekt:

  • Muuda lehe nimi:

Anna lehele sobiv nimi, näiteks “files,” et struktureerida andmed paremini.

  • Ühenda tabel Google Scriptiga:

Ava “Extensions” → “Apps Script,” et alustada skripti loomist, mis seostub tabeliga.

Kuidas kood töötab

1. Algatamine ja seadistused


function listAllFilesAndFolders() {
  // Google Drive'i kausta ID, mida soovite skaneerida
  const parentFolderId = '[Google Drive'i kausta ID, mida soovite skaneerida]';
  // Saame tabeli objekti, kuhu kirjutame teavet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('files');
  // Tühjendame tabeli, et eemaldada vanad andmed
  sheet.clear();
// Lisame andmete lihtsaks salvestamiseks veerupealkirjad
  sheet.appendRow(['File name', 'File ID', 'MIME type', 'Parent foldert']);


Kausta ID võib leida aadressiribas


Lehe nimi meie näites oli “files”.

2. Andmete ettevalmistamine töötlemiseks

  • Vanemkausta objekti hankimine: const parentFolder = DriveApp.getFolderById(parentFolderId).
// Saame vanemkausta objekti selle ID järgi
  const parentFolder = DriveApp.getFolderById(parentFolderId);
  • Algse järjekorra loomine kaustade ja failide töötlemiseks: const queue.
// Loome järjekorra kaustade ja failide töötlemiseks
  // Iga kirje sisaldab kausta ja selle vanemkausta nime
  const queue = [{folder: parentFolder, parentName: ''}];

3. Kaustade töötlemise põhitsükkel

  • Tsükkel while (queue.length > 0) — protsess jätkub seni, kuni järjekorras on elemente.

  • Järjekorra esimese elemendi eemaldamine: const current = queue.shift().

  • Failide töötlemine praeguses kaustas:

    • Failide loendi hankimine: current.folder.getFiles().
    • Tsükkel while (files.hasNext()) failide andmete tabelisse lisamiseks.
  • Failide info lisamine tabelisse: sheet.appendRow().
// Tsükkel töötab seni, kuni järjekorras on elemente (kaustasid)
  while (queue.length > 0) {
    // Võtame järjekorra esimese elemendi (kausta ja selle vanemkausta nimi)
    const current = queue.shift();
    // Saame failide loendi praeguses kaustas
    const files = current.folder.getFiles(); // Algses koodis oli current.getFiles(); aga see variant ei töötanud
    // Tsükkel töötab iga faili kohta kaustas
    while (files.hasNext()) {
      const file = files.next(); // Saame faili
      // Lisame tabelisse faili andmed
      sheet.appendRow([
        file.getName(),        // Faili nimi
        file.getId(),          // Faili ID
        file.getMimeType(),    // Faili MIME tüüp
        current.folder.getName() // Praeguse (vanem)kausta nimi
      ]);
    }


4. Alamkaustade töötlemine

  • Alamkaustade loendi hankimine: current.folder.getFolders().
  • Tsükkel while (subFolders.hasNext()) alamkaustade järjekorda lisamiseks.
  • Alamkaustade järjekorda lisamine: queue.push().

// Saame alamkaustade loendi praeguses kaustas
    const subFolders = current.folder.getFolders();
    // Tsükkel töötab iga alamkausta kohta
    while (subFolders.hasNext()) {
      const subFolder = subFolders.next(); // Saame alamkausta
      // Lisame alamkausta järjekorda edasiseks töötlemiseks
      queue.push({
        folder: subFolder,                // Alamkaust
        parentName: current.folder.getName() // Selle vanemkausta nimi
      });

5. Parandused ja täpsustused

  • Lühike selgitus parandusest: koodimuudatus current.getFiles() asemel current.folder.getFiles(), kuna algne variant põhjustas vea.

// Saame failide loendi praeguses kaustas
    const files = current.folder.getFiles(); // Algses koodis oli current.getFiles(); aga see variant ei töötanud

  • Lisasin kogu koodi sisse kommentaarid.

Kogu kood kommentaaritega.

function listAllFilesAndFolders() {
  // Google Drive'i kausta ID, mida soovite skaneerida
  const parentFolderId = '[Google Drive'i kausta ID, mida soovite skaneerida]';
  // Saame tabeli objekti, kuhu kirjutame teavet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('files');
  // Tühjendame tabeli, et eemaldada vanad andmed
  sheet.clear();
  // Lisame andmete lihtsaks salvestamiseks veerupealkirjad
  sheet.appendRow(['Faili nimi', 'Faili ID', 'MIME tüüp', 'Vanem kaust']);
  // Saame vanemkausta objekti selle ID järgi
  const parentFolder = DriveApp.getFolderById(parentFolderId);
  // Loome järjekorra kaustade ja failide töötlemiseks
  // Iga kirje sisaldab kausta ja selle vanemkausta nime
  const queue = [{folder: parentFolder, parentName: ''}];
  // Tsükkel töötab seni, kuni järjekorras on elemente (kaustasid)
  while (queue.length > 0) {
    // Võtame järjekorra esimese elemendi (kausta ja selle vanemkausta nimi)
    const current = queue.shift();
    // Saame failide loendi praeguses kaustas
    const files = current.folder.getFiles(); // Algses koodis oli current.getFiles(); aga see variant ei töötanud
    // Tsükkel töötab iga faili kohta kaustas
    while (files.hasNext()) {
      const file = files.next(); // Saame faili
      // Lisame tabelisse faili andmed
      sheet.appendRow([
        file.getName(),        // Faili nimi
        file.getId(),          // Faili ID
        file.getMimeType(),    // Faili MIME tüüp
        current.folder.getName() // Praeguse (vanem)kausta nimi
      ]);
    }
    // Saame alamkaustade loendi praeguses kaustas
    const subFolders = current.folder.getFolders();
    // Tsükkel töötab iga alamkausta kohta
    while (subFolders.hasNext()) {
      const subFolder = subFolders.next(); // Saame alamkausta
      // Lisame alamkausta järjekorda edasiseks töötlemiseks
      queue.push({
        folder: subFolder,                // Alamkaust
        parentName: current.folder.getName() // Selle vanemkausta nimi
      });
    }
  }
}


Mis muudatused tegin koodis?

1. Lisasin vajalik Kausta ID

 // Kausta ID, mille sisu soovid Google Drive'ist läbi otsida
  const parentFolderId = '1AcF94sHTBnuNjIWYZ9cS46XpfpiVqJtM';

2. Failide ikoonid ja metaandmed:

  • Parandatud koodis lisati failidele visuaalsed ikoonid vastavalt MIME tüübile (näiteks: PDF-failid – 📝, pildid – 🖼️, videod – 🎥).

/**
 * Faili ikooni määramine MIME tüübi järgi.
 */
function getFileIcon(mimeType) {
  return mimeType.startsWith('application/pdf') ? '📝' :
         mimeType.startsWith('image') ? '🖼️' :
         mimeType.startsWith('video') ? '🎥' :
         '📄';
}

  • Tabelisse lisati metaandmed nagu faili loomise kuupäev (Created Time) ja viimase muutmise aeg (Last Updated), mida esialgses koodis polnud.

// Faili andmete kirjutamine tabelisse
      sheet.getRange(currentRow, 1, 1, headers.length).setValues([[
        icon,
        file.getName(),
        file.getId(),
        file.getMimeType(),
        current.folder.getName(),
        file.getDateCreated(),
        file.getLastUpdated()
      ]]);

3. Tabeli pealkirjade täiendamine:

  • Originaalkoodis oli tabeli veerupealkirjadeks: ['Faili nimi', 'Faili ID', 'MIME tüüp', 'Vanem kaust'].
  • Täiendatud versioonis lisati rohkem veerge ja pealkirjad inglise keeles: ['File Icon', 'File Name', 'File ID', 'MIME Type', 'Parent Folder', 'Created Time', 'Last Updated'].

// Tabeli pealkirjade seadistamine
  const headers = ['File Icon', 'File Name', 'File ID', 'MIME Type', 'Parent Folder', 'Created Time', 'Last Updated'];
  formatHeaders(sheet, headers);

4. Tabeli vormindamine:

  • Täiendatud koodis tabeli veerupealkirjad vormindati visuaalselt: halli taustaga, rasvases kirjas ja tsentreeritud. Originaalkoodis selline vormindamine puudus.
  • Lisati automaatne veergude laiuse kohandamine (autoAdjustColumnWidths), mis originaalkoodis polnud.

/**
 * Tabeli pealkirjade vormindamine.
 */
function formatHeaders(sheet, headers) {
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length)
    .setBackground('#D3D3D3')
    .setFontWeight('bold')
    .setHorizontalAlignment('center')
    .setBorder(true, true, true, true, true, true);
}

5. Viimati lisatud ja muudetud failide esiletõstmine:

  • Parandatud versioonis lisati värvikoodid: viimane lisatud fail (#94FFD4, roheline) ja viimane muudetud fail (#ABE7FE, sinine) märgitakse tabelis visuaalselt esile.

/**
 * Viimaste muudatuste esiletõstmine tabelis.
 */
function highlightLastUpdates(sheet, lastAddedRow, lastUpdatedRow, rowCount, columnCount) {
  if (rowCount > 0) {
    if (lastAddedRow) {
      sheet.getRange(lastAddedRow, 1, 1, columnCount)
        .setBackground('#94FFD4')
        .setFontWeight('bold');
    }
    if (lastUpdatedRow && lastUpdatedRow !== lastAddedRow) {
      sheet.getRange(lastUpdatedRow, 1, 1, columnCount)
        .setBackground('#ABE7FE')
        .setFontWeight('bold');
    }
    autoAdjustColumnWidths(sheet);
  } else {
    sheet.getRange(2, 2).setValue('Faile ei leitud');
  }
}

6. Funktsiooni struktureerimine:

  • Originaalkood oli kirjutatud ühe suure funktsioonina.
  • Muutud kood jagati eraldi funktsioonideks:

    • clearSheet,
    • formatHeaders,
    • getFileIcon,
    • addSubfoldersToQueue, ja
    • highlightLastUpdates.
  • See muudatus muudab koodi paremini loetavaks ja lihtsamini hooldatavaks.

/**
 * Töölehe sisu puhastamine.
 */
function clearSheet(sheet) {
  sheet.clear();
}

7. Loogikavea parandamine:

  • Originaalkoodis oli loogikaviga: current.getFiles(). Originaal koodi omanikul see variant töötas. Mul ei töötanud ja see asendati parandatud variandiga: current.folder.getFiles().

8. Vigade käsitlemine:

  • Kui kaustas pole ühtegi faili, kuvab täiendatud kood tabelis teate Faile ei leitud. Originaalkood ei andnud sellist tagasisidet.

9. Tabeli veergude automaatne laiuse kohandamine:

  • Lisati funktsioon, mis automaatselt kohandab tabeli veergude laiuse vastavalt sisu pikkusele. Lisaks määrati fikseeritud laius esimesele veerule (“Faili ikoon”).

/**
 * Tabeli veergude automaatne laiuse kohandamine.
 */
function autoAdjustColumnWidths(sheet) {
  const lastColumn = sheet.getLastColumn();
  for (let i = 1; i <= lastColumn; i++) {
    sheet.autoResizeColumn(i);
  }
  sheet.setColumnWidth(1, 80);
  sheet.setColumnWidth(2, 100);
  sheet.setColumnWidth(3, 300);
  sheet.setColumnWidth(5, 100);
  sheet.setColumnWidth(6, 100);
}

Kogu kood minu muutusega

/**
 * Skript Google Drive'i määratud kausta skaneerimiseks.
 * Kuvab kõigi failide ja alamkaustade loendi Google Sheetsis.
 * Tabeli pealkirjad on fikseeritud.
 */
function listAllFilesAndFolders() {
  // Google Drive'i juurkausta ID
  const folderId = '1AcF94sHTBnuNjIWYZ9cS46XpfpiVqJtM';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('files');
  // Töölehe sisu puhastamine enne töö alustamist
  clearSheet(sheet);
  // Tabeli pealkirjade seadistamine
  const headers = ['File Icon', 'File Name', 'File ID', 'MIME Type', 'Parent Folder', 'Created Time', 'Last Updated'];
  formatHeaders(sheet, headers);
  // Failide ja kaustade töötlemise ettevalmistamine
  const parentFolder = DriveApp.getFolderById(folderId);
  const queue = [{ folder: parentFolder, parentName: '' }];
  let lastAddedRow = null, lastUpdatedRow = null, lastUpdateDate = null, rowCount = 0;
  // Peamine failide ja kaustade töötlemise osa
  while (queue.length > 0) {
    const current = queue.shift();
    const files = current.folder.getFiles();
    // Praeguse kausta failide lisamine
    while (files.hasNext()) {
      const file = files.next();
      const currentRow = sheet.getLastRow() + 1;
      rowCount++;
      const icon = getFileIcon(file.getMimeType());
      // Faili andmete kirjutamine tabelisse
      sheet.getRange(currentRow, 1, 1, headers.length).setValues([[
        icon,
        file.getName(),
        file.getId(),
        file.getMimeType(),
        current.folder.getName(),
        file.getDateCreated(),
        file.getLastUpdated()
      ]]);
      // Viimati lisatud faili märkimine
      lastAddedRow = currentRow;
      // Viimati muudetud faili märkimine
      const lastUpdated = file.getLastUpdated();
      if (!lastUpdateDate || lastUpdated > lastUpdateDate) {
        lastUpdateDate = lastUpdated;
        lastUpdatedRow = currentRow;
      }
    }
    // Alamkaustade lisamine töötlemisjärjekorda
    addSubfoldersToQueue(queue, current.folder);
  }
  // Viimaste muudatuste esiletõstmine tabelis
  highlightLastUpdates(sheet, lastAddedRow, lastUpdatedRow, rowCount, headers.length);
}
/**
 * Töölehe sisu puhastamine.
 */
function clearSheet(sheet) {
  sheet.clear();
}
/**
 * Tabeli pealkirjade vormindamine.
 */
function formatHeaders(sheet, headers) {
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length)
    .setBackground('#D3D3D3')
    .setFontWeight('bold')
    .setHorizontalAlignment('center')
    .setBorder(true, true, true, true, true, true);
}
/**
 * Faili ikooni määramine MIME tüübi järgi.
 */
function getFileIcon(mimeType) {
  return mimeType.startsWith('application/pdf') ? '📝' :
         mimeType.startsWith('image') ? '🖼️' :
         mimeType.startsWith('video') ? '🎥' :
         '📄';
}
/**
 * Alamkaustade lisamine töötlemisjärjekorda.
 */
function addSubfoldersToQueue(queue, folder) {
  const subFolders = folder.getFolders();
  while (subFolders.hasNext()) {
    queue.push({
      folder: subFolders.next(),
      parentName: folder.getName()
    });
  }
}
/**
 * Viimaste muudatuste esiletõstmine tabelis.
 */
function highlightLastUpdates(sheet, lastAddedRow, lastUpdatedRow, rowCount, columnCount) {
  if (rowCount > 0) {
    if (lastAddedRow) {
      sheet.getRange(lastAddedRow, 1, 1, columnCount)
        .setBackground('#94FFD4')
        .setFontWeight('bold');
    }
    if (lastUpdatedRow && lastUpdatedRow !== lastAddedRow) {
      sheet.getRange(lastUpdatedRow, 1, 1, columnCount)
        .setBackground('#ABE7FE')
        .setFontWeight('bold');
    }
    autoAdjustColumnWidths(sheet);
  } else {
    sheet.getRange(2, 2).setValue('Faile ei leitud');
  }
}
/**
 * Tabeli veergude automaatne laiuse kohandamine.
 */
function autoAdjustColumnWidths(sheet) {
  const lastColumn = sheet.getLastColumn();
  for (let i = 1; i <= lastColumn; i++) {
    sheet.autoResizeColumn(i);
  }
  sheet.setColumnWidth(1, 80);
  sheet.setColumnWidth(2, 100);
  sheet.setColumnWidth(3, 300);
  sheet.setColumnWidth(5, 100);
  sheet.setColumnWidth(6, 100);
}

Tulemus

Kokkuvõtte

Tehtud muudatused laiendasid skripti funktsionaalsust, muutsid tabeli visuaalselt arusaadavamaks ja pakkusid kasutajale täpsemat ja selgemat tagasisidet failide kohta. Kood on nüüd paremini struktureeritud, hooldatav ning pakub rohkem kasutajamugavust.