CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Warren Thornton
Warren Thornton

Reputation: 305

Scan Sheet Files for Certain Cell Values then put Corresponding Spreadsheet ID in certain cells

So I want to create and automatic system for scanning (file iterating) a particular folder. What I want the system to do is:

Scan all the sheet files in the folder, and when a certain cell on the first sheet of one of the spreadsheets (they are all sheet files) contains a certain cell value (for example: when cell A1 of the first sheet "Sheet1" contains "Sales Report") to then find the file ID of that particular spreadsheet and insert it into a cell on another fixed spreadsheet.

Could someone give me an idea of what this would like look in the most efficient manner, I've got a few ideas in regards to if/else arguments but I fear I would make it more complicated than it needs to be. Any help would be appreciated, thank you!

Upvotes: 0

Views: 51

Answers (1)

Cooper
Cooper

Reputation: 64140

This should get you started:

Instructions:

  1. Load into Script Editor and run any function and it will check for any additional authentication requirements so that you can provide that authorization before attempting to use.

  2. Create a sheet name Master so that I can appead spreadsheet name, sheet name and spreadsheet id to that sheet

  3. Get folder Id and paste it into the code where shown

    function scanSpreadsheetsInAFolder() {
      const ss=SpreadsheetApp.getActive();
      const tsh=ss.getSheetByName('Master');//where I store ssname shname id
      const folder=DriveApp.getFolderById('FolderId');
      let files=folder.getFilesByType(MimeType.GOOGLE_SHEETS);
      while(files.hasNext()) {
        let file=files.next();
        let s=SpreadsheetApp.openById(file.getId());
        let sh=s.getSheets()[0];//gets first sheet in spreadsheet
        let v=sh.getRange(1,1).getValue();//gets value in A1
        if(v=='Sales Report') {
          tsh.appendRow([ss.getName(),sh.getName(),ss.getId()]);//append data to master
        }
      }
    }

Upvotes: 1

Related Questions