Is it possible to get a google doc modified date within a google sheets or apps script?-CodePudding

I have a google doc urls column within a google sheets

https://docs.google.com/spreadsheets/d/id

I want to display another column next to that to display google docs latest modified date? Is it possible to achieve that using google sheets formulas or apps script?

CodePudding user response：

About Is it possible to achieve that using google sheets formulas or apps script?, in this case, the custom function cannot be used. But, when the Google Apps Script is run with the script editor, custom menu, button and so on, your goal can be achieved. In your situation, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and run the function with the script editor. By this, the script is run.

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
  const range = sheet.getRange("A2:A"   sheet.getLastRow());
  const values = range.getValues()
    .map(([a]) => {
      if (a.toString() == "") return [null];
      const id = a.split("/")[5];
      return [id ? DriveApp.getFileById(id).getLastUpdated() : null];
    });
  range.offset(0, 1).setValues(values);
}

In this sample script, first, the URLs are retrieved from the column "A" and the last updated date is retrieved using the file ID retrieved from URL. And, the retrieved values are put to the column "B".

Note:

In this sample, it supposes that the format of your URL is like https://docs.google.com/spreadsheets/d/id. Please be careful about this.

At the above sample script, it supposes that the URLs like https://docs.google.com/spreadsheets/d/id are directly put into the column "A". But, if the URLs are put to the column "A" as the hyperlink of a text like "DOC", please test the following script.

  function myFunction() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
    const range = sheet.getRange("A2:A"   sheet.getLastRow());
    const values = range.getRichTextValues()
      .map(([a]) => {
        const link = a.getLinkUrl();
        if (!link) return [null];
        const id = link.split("/")[5];
        return [id ? DriveApp.getFileById(id).getLastUpdated() : null];
      });
    range.offset(0, 1).setValues(values);
  }

As another approach, for example, if the number of URLs is large, I thought that the following sample script might be useful as the low process cost. This script retrieves the last updated date using Drive API with batch requests. When you use this script, please install a Google Apps Script library and please enable Drive API at Advanced Google services, and test it.

  function myFunction() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
    const range = sheet.getRange("A2:A"   sheet.getLastRow());
    const requests = {
      batchPath: "batch/drive/v3",
      requests: range.getValues().map(([a]) => ({ method: "GET", endpoint: `https://www.googleapis.com/drive/v3/files/${a.split("/")[5]}?fields=modifiedTime` })),
    };
    const values = BatchRequest.EDo(requests).map(({ modifiedTime }) => [modifiedTime ? new Date(modifiedTime) : null]);
    range.offset(0, 1).setValues(values);
  }

In this script, as a sample, it supposes that the column "A" has the URLs like https://docs.google.com/spreadsheets/d/id. Please be careful about this.

Sample script:

Note:

References: