Get latest google sheets with its SpreasheetID and SheetID from google drive

user1549124 0
Viewed 51

I want to get sheetID from Spreadsheet/file that I searched within drive. I get the spreadsheetID but not the sheetID.

Here's the function:

function searchSheets(){
     var files = DriveApp.searchFiles('title contains "latest"');
     while (files.hasNext()) {
     var file = files.next();
     var ss = file.getId();
     var sss_ID = SpreadsheetApp.openById(ss).getSheetId();
     Logger.log(sss_ID);
}
}

Output: enter image description here

google-drive-api google-sheets google-apps-script
1 Answers

AFAIK there is no efficient way to get the latest spreadsheet by using the DriveApp.searchFiles method (the OP's code search for files having latest string in the file title).

One option is to use the Advanced Drive Service:

/**
 * Logs the file name, id, creation date and URL of the most recent spreadsheet
 */
function searchSheets() {
  const response = Drive.Files.list({
    /** 
     * Required properties 
     * @property {string} q - Query
     * @property {string} orderBy - Required because list returns paged results
     */
    q: `mimeType='application/vnd.google-apps.spreadsheet'`,
    orderBy: 'createdDate desc',
    /** 
     * Optional properties, 
     * @property {string} fields - Limits the fields to be included in the 
     * response. When omitted, all the File fields will be included 
     */
    fields: "items(id,title,createdDate,alternateLink)"
  })

  const newest = response.items[0];
  console.log(newest.title, newest.id, new Intl.DateTimeFormat('en-US', {
    timeZone: 'America/Mexico_City',
    timeZoneName: 'short'
  }).format(new Date(newest.createdDate)), newest.alternateLink);
}

Once you get the id of the latest spreadsheet, it might be used to open it using the Spreadsheet Service or the Advanced Google Sheets Service to get the id.

Related

userH4H60
Related