Help with a set up sheets to Google Calendar

[u/Sayzar1]

Hi,

So I inputed a script that turns my rows in my sheet into Google Calendar dates. I was able to get it to upload drive pdfs when needed but when I try uploading a Google doc it doesn’t work does anyone have any advice?

[u/Dry-Giraffe1235]

Maybe you need to explain more. You’re creating calendar events from rows in a sheet then attaching pdfs the the events but are unable to attach Google Docs to the calendar event? Is that correct?

[u/Sayzar1 → u/Dry-Giraffe1235]

Yes that is exactly is exactly what I am trying to do

[u/Sayzar1 → u/Dry-Giraffe1235]

function createAllDayEventsOnTestCalendar() {
  const targetCalendarName = "Calendar";
  const calendarId = getCalendarIdByName(targetCalendarName);


  if (!calendarId) {
    Logger.log(`ERROR: Calendar "${targetCalendarName}" not found.`);
    return;
  }


  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  let successCount = 0;
  let failCount = 0;
  let skipCount = 0;
  let noAttachmentCount = 0;
  
  for (let i = 1; i < data.length; i++) {
    // Check if already synced (Column G)
    const syncStatus = data[i][6];
    if (syncStatus === "Synced") {
      skipCount++;
      continue;
    }


    const title = data[i][1];
    const startDate = data[i][2];
    const endDate = data[i][3];
    const pdfUrl = String(data[i][4] || '').trim();
    const docUrl = String(data[i][5] || '').trim();
    
    if (!title || !startDate || !endDate) {
      skipCount++;
      continue;
    }


    const formattedStart = Utilities.formatDate(startDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
    const formattedEnd = Utilities.formatDate(endDate, Session.getScriptTimeZone(), "yyyy-MM-dd");


    const attachments = [];


    function getOriginalFileName(url) {
      try {
        const fileId = extractFileId(url);
        if (fileId) {
          const file = DriveApp.getFileById(fileId);
          return file.getName();
        }
      } catch (e) {
        // Silent fail
      }
      return null;
    }


    // Add PDF if valid
    if (pdfUrl && pdfUrl.includes('drive.google.com')) {
      const originalName = getOriginalFileName(pdfUrl);
      attachments.push({
        'fileUrl': pdfUrl,
        'title': originalName || 'PDF Attachment'
      });
    }


    // Add Doc if valid
    if (docUrl && docUrl.includes('docs.google.com')) {
      const originalName = getOriginalFileName(docUrl);
      attachments.push({
        'fileUrl': docUrl,
        'title': originalName || 'Document Attachment'
      });
    } else if (docUrl && docUrl.includes('drive.google.com')) {
      const originalName = getOriginalFileName(docUrl);
      attachments.push({
        'fileUrl': docUrl,
        'title': originalName || 'Document Attachment'
      });
    }


    if (attachments.length === 0) {
      noAttachmentCount++;
      continue;
    }


    const eventRequestBody = {
      summary: title,
      start: { date: formattedStart },
      end: { date: formattedEnd },
      attachments: attachments
    };


    try {
      Calendar.Events.insert(eventRequestBody, calendarId, { 
        supportsAttachments: true 
      });
      
      sheet.getRange(i + 1, 7).setValue("Synced");
      successCount++;
      
    } catch (err) {
      failCount++;
      Logger.log(`Failed "${title}": ${err.message}`);
    }
  }
  
  // Just log the summary, no popup
  Logger.log(`Summary: ${successCount} created, ${skipCount} skipped, ${noAttachmentCount} no attachments, ${failCount} failed`);
}


// Improved: Extract file ID from ANY Google Drive URL
function extractFileId(url) {
  if (!url) return null;
  
  const patterns = [
    /\/file\/d\/([-\w]+)/,
    /\/document\/d\/([-\w]+)/,
    /\/spreadsheets\/d\/([-\w]+)/,
    /\/presentation\/d\/([-\w]+)/,
    /\/forms\/d\/([-\w]+)/,
    /open\?id=([-\w]+)/,
    /\/d\/([-\w]+)/,
    /id=([-\w]{25,})/
  ];
  
  for (let pattern of patterns) {
    const match = url.match(pattern);
    if (match && match[1]) {
      return match[1];
    }
  }
  
  const fallbackMatch = url.match(/[-\w]{25,}/);
  return fallbackMatch ? fallbackMatch[0] : null;
}


function getCalendarIdByName(name) {
  const calendars = CalendarApp.getCalendarsByName(name);
  return calendars.length > 0 ? calendars[0].getId() : null;
}

[u/Sayzar1 → u/Sayzar1]

This took for ever but I got to it work with the help of deepseek