Retrieving List of All Emails of Microsoft Account using Google Apps Script

GasTips / Library / spreadsheet
Google Apps Script / library / OneDrive / spreadsheet

Gists

This is a sample script for retrieving the list of all emails of Microsoft account and putting them to Google Spreadsheet using Google Apps Script.

I updated OnedriveApp to v1.2.0 by adding 1 method for retrieving the access token and 7 methods for managing emails of Microsoft account. By this, the emails got to be able to be gotten and sent using Microsoft account using OnedriveApp with Google Apps Script.

Before you use this script, please install [OnedriveApp] which is Google Apps Script library. Ref And, please authorize your Microsoft account for using Microsoft Graph API. Ref

Sample script

After you authorized your Microsoft account and retrieved the access token, you can use the following sample script.

function myFunction() {
  // 1. Retrieve mail list using OnedriveApp.
  const obj = {
    select: ["createdDateTime", "sender", "subject", "bodyPreview"],
    orderby: "createdDateTime",
    order: "asc",
  };

  const prop = PropertiesService.getScriptProperties();
  const odapp = OnedriveApp.init(prop);
  const res = odapp.getEmailList(obj);

  // 2. Convert object to array for putting to Spreadsheet.
  const headers = ["createdDateTime", "sender", "subject", "bodyPreview", "id"];
  const values = res.reduce(
    (ar, e) =>
      ar.concat([
        headers.map((h) =>
          h == "createdDateTime"
            ? new Date(e[h])
            : h == "sender"
            ? e[h]
              ? `${e[h].emailAddress.address}(${e[h].emailAddress.name})`
              : ""
            : e[h] || ""
        ),
      ]),
    [headers]
  );

  // 3. Put the values to Spreadsheet.
  const ssId = "###"; // Please set the Spreadsheet ID.
  const sheet = SpreadsheetApp.openById(ssId).getSheetByName("Sheet1"); // Please set the sheet name.
  sheet
    .clearContents()
    .getRange(1, 1, values.length, values[0].length)
    .setValues(values);
}

When you run this script, the email list of your Microsoft account is retrieved and put to the Spreadsheet as the above demo image.

 Share!