Apps Scripts Code

Copy Code

// =====================
// Adds the custom menu and initializes the sheet headers
function onOpen() {
    SpreadsheetApp.getUi()
      .createMenu('OneDrive Integration')
      .addItem('Enter Credentials', 'showSidebar')
      .addItem('Authorize', 'showAuthorizationUrl')
      .addItem('Copy Files', 'copyAllFilesToOneDrive')
      .addItem('Reset Credentials', 'resetCredentials')
      .addToUi();
  
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const headers = sheet.getRange(1, 1, 1, 2).getValues()[0];
    if (headers[0] !== 'Name' || headers[1] !== 'Status') {
      sheet.getRange(1, 1, 1, 2).setValues([['Name', 'Status']]);
    }
  }
  
  // Displays the credentials sidebar
  function showSidebar() {
    const html = HtmlService.createHtmlOutputFromFile('Sidebar')
      .setTitle('Enter Microsoft Graph Credentials')
      .setWidth(300);
    SpreadsheetApp.getUi().showSidebar(html);
  }
  
  // Resets all stored credentials
  function resetCredentials() {
    PropertiesService.getUserProperties().deleteAllProperties();
    SpreadsheetApp.getUi().alert('✅ Credentials have been reset.');
  }
  
  // Saves credentials from the sidebar form
  function saveCredentials(data) {
    const props = PropertiesService.getUserProperties();
    props.setProperty('CLIENT_ID', data.clientId.trim());
    props.setProperty('CLIENT_SECRET', data.clientSecret.trim());
    props.setProperty('TENANT_ID', data.tenantId.trim());
    props.setProperty('REDIRECT_URI', data.redirectUri.trim());
  }
  
  // Auth service
  function getOAuthService() {
    const props = PropertiesService.getUserProperties();
    return OAuth2.createService('OneDrive')
      .setAuthorizationBaseUrl(`https://login.microsoftonline.com/${props.getProperty('TENANT_ID')}/oauth2/v2.0/authorize`)
      .setTokenUrl(`https://login.microsoftonline.com/${props.getProperty('TENANT_ID')}/oauth2/v2.0/token`)
      .setClientId(props.getProperty('CLIENT_ID'))
      .setClientSecret(props.getProperty('CLIENT_SECRET'))
      .setCallbackFunction('authCallback')
      .setPropertyStore(props)
      .setScope('https://graph.microsoft.com/Files.ReadWrite.All offline_access')
      .setTokenHeaders({ 'Content-Type': 'application/x-www-form-urlencoded' });
  }
  
  // OAuth callback
  function authCallback(request) {
    const service = getOAuthService();
    const authorized = service.handleCallback(request);
    return HtmlService.createHtmlOutput(authorized ? '✅ Authorized' : '❌ Authorization failed');
  }
  
  // Shows the auth URL
  function showAuthorizationUrl() {
    const service = getOAuthService();
    const ui = SpreadsheetApp.getUi();
  
    if (!service.hasAccess()) {
      const url = service.getAuthorizationUrl();
      ui.alert(
        '🔗 Authorization Required',
        'Follow these steps:\n\n1. Copy and open the URL below in a browser:\n\n' +
        url +
        '\n\n2. Grant access.\n3. Return and click "Copy Files".',
        ui.ButtonSet.OK
      );
    } else {
      ui.alert('✅ Already Authorized', 'You can now use "Copy Files".', ui.ButtonSet.OK);
    }
  }
  
  function copyAllFilesToOneDrive() {
    const service = getOAuthService();
    if (!service.hasAccess()) {
      SpreadsheetApp.getUi().alert('❌ Please authorize first via the "Authorize" menu option.');
      return;
    }
  
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    sheet.getRange(2, 1, sheet.getLastRow(), 2).clearContent(); // clear old logs
    const allFiles = DriveApp.getFiles();
    let row = 2;
  
    while (allFiles.hasNext()) {
      const file = allFiles.next();
      const name = file.getName();
      const id = file.getId();
      const mime = file.getMimeType();
      let blob;
      let status = '';
  
      try {
        if (mime === MimeType.GOOGLE_DOCS) {
          blob = UrlFetchApp.fetch(`https://www.googleapis.com/drive/v3/files/${id}/export?mimeType=application/vnd.openxmlformats-officedocument.wordprocessingml.document`, {
            headers: {
              Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
            }
          }).getBlob().setName(name + '.docx');
        } else if (mime === MimeType.GOOGLE_SHEETS) {
          blob = UrlFetchApp.fetch(`https://www.googleapis.com/drive/v3/files/${id}/export?mimeType=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet`, {
            headers: {
              Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
            }
          }).getBlob().setName(name + '.xlsx');
        } else if (mime === MimeType.GOOGLE_SLIDES) {
          blob = UrlFetchApp.fetch(`https://www.googleapis.com/drive/v3/files/${id}/export?mimeType=application/vnd.openxmlformats-officedocument.presentationml.presentation`, {
            headers: {
              Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
            }
          }).getBlob().setName(name + '.pptx');
        } else if (mime === MimeType.GOOGLE_APPS_SCRIPT) {
          throw new Error('Skipping Google Apps Script files (unsupported export)');
        } else {
          blob = file.getBlob();
        }
  
        const uploadUrl = `https://graph.microsoft.com/v1.0/me/drive/root:/${blob.getName()}:/content`;
  
        const response = UrlFetchApp.fetch(uploadUrl, {
          method: 'PUT',
          contentType: blob.getContentType(),
          payload: blob.getBytes(),
          headers: {
            Authorization: 'Bearer ' + service.getAccessToken()
          }
        });
  
        status = `✅ Uploaded (${response.getResponseCode()})`;
      } catch (err) {
        status = `❌ Error: ${err.message}`;
      }
  
      sheet.getRange(row, 1, 1, 2).setValues([[name, status]]);
      row++;
    }
    formatStatusSheet();
  }
  function formatStatusSheet() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const range = sheet.getDataRange();
    const values = range.getValues();
  
    // Format header row
    const headerRange = sheet.getRange(1, 1, 1, range.getNumColumns());
    headerRange.setFontWeight('bold');
    headerRange.setBackground('#d9e1f2');
  
    // Loop through rows and color code the status column
    for (let i = 2; i <= values.length; i++) {
      const status = sheet.getRange(i, 2).getValue(); // Status in column B
  
      if (status.toString().includes('✅')) {
        sheet.getRange(i, 2).setFontColor('green');
      } else if (status.toString().includes('❌')) {
        sheet.getRange(i, 2).setFontColor('red');
      } else {
        sheet.getRange(i, 2).setFontColor('black');
      }
    }
  
    // Auto resize columns
    sheet.autoResizeColumns(1, 2);
  
  
  
    // Add borders
    range.setBorder(true, true, true, true, true, true);
  }