{"id":52636,"date":"2025-03-12T14:00:00","date_gmt":"2025-03-12T13:00:00","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=52636"},"modified":"2025-08-20T21:48:29","modified_gmt":"2025-08-20T19:48:29","slug":"extrair-o-calendario-do-google-usando-o-script-de-aplicativos","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/pt\/blog\/google-sheets-formulas\/pull-google-calendar-using-apps-script\/","title":{"rendered":"Extrair dados do Google Agenda para o Planilhas Google usando o Apps Script"},"content":{"rendered":"

[et_pb_section fb_built=”1″ _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n

\n

This post was originally published in our community forum. <\/strong><\/p>\n<\/blockquote>\n

Objective :<\/strong>\u00a0<\/span>This guide demonstrates how to use Google Apps Script to pull data from Google Calendar and display them in a Google Sheet. This process allows you to automate the retrieval of calendar events and manage them directly within a spreadsheet.<\/p>\n

Create a New Google Sheet:<\/strong>\u00a0<\/span>Open Google Sheets and create a new spreadsheet or open an existing one where you want the calendar data to be displayed.<\/p>\n

Open Apps Script:<\/strong>\u00a0<\/span>In your Google Sheet, click on\u00a0<\/span>Extensions<\/code>\u00a0<\/span>>\u00a0<\/span>Apps Script<\/code>.<\/p>\n

Insert the Script:<\/strong>\u00a0<\/span>In your Apps Script project, you\u2019ll have two files:\u00a0<\/span>Code.gs<\/code>\u00a0<\/span>and\u00a0<\/span>props.gs<\/code>. Delete any existing code in the script editor and replace it with the code snippet provided below. Copy the provided code snippet for\u00a0<\/span>Code.gs<\/code>\u00a0<\/span>and\u00a0<\/span>props.gs<\/code>\u00a0<\/span>into their respective files.<\/p>\n

props.gs<\/code><\/strong><\/p>\n

const<\/span> emailIds = [\n\u00a0 '' \/\/ Include here the email address<\/span>\n];<\/code><\/pre>\n
Code.gs<\/code><\/strong><\/p>\n
function fetchCalendarData<\/span>()<\/span> {\n\n  \/\/ Get the active sheet<\/span>\n  const<\/span> sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();\n  sheet.clear();  \/\/ Clear any previous content<\/span>\n\n  sheet.appendRow([\"Meeting ID\"<\/span>, \"Name\"<\/span>, \"Attendees\"<\/span>, \"Duration\"<\/span>, \"Video Link\"<\/span>]);     \/\/ Set headers<\/span>\n\n  \/\/ Loop through each email ID<\/span>\n  emailIds.forEach(email => {\n    const<\/span> events = CalendarApp.getCalendarById(email).getEvents(new<\/span> Date('start_date'<\/span>), new<\/span> Date()); \n    \/\/ Loop through events and write them to the sheet<\/span>\n    events.forEach(event<\/span> => {\n      const<\/span> eventId = event<\/span>.getId();\n      const<\/span> eventName = event<\/span>.getTitle();\n      const<\/span> attendees = event<\/span>.getGuestList().map(guest => guest.getEmail()).join<\/span>(', '<\/span>);\n      const<\/span> duration = (event<\/span>.getEndTime() - event<\/span>.getStartTime()) \/ (1000<\/span> * 60<\/span>); \n      let<\/span> videoLink = \"\"<\/span>;\n      const<\/span> description = event<\/span>.getDescription();\n      if<\/span> (description) {\n        const<\/span> meetLinkRegex = \/https:\\\/\\\/calendly\\.com\\\/events\\\/[\\w-]+\\\/google_meet\/i; \/\/ Regex to match Video link<\/span>\n        const<\/span> match = description.match(meetLinkRegex);\n        if<\/span> (match) {\n          videoLink = match[0<\/span>]; \n        }\n      } \n      sheet.appendRow([eventId, eventName, attendees, duration, videoLink]);\n    });\n  });\n\n  Logger.log(\"Calendar data has been fetched and updated in the sheet.\"<\/span>);\n}<\/code><\/pre>\n
Update Email IDs:<\/strong>\u00a0<\/span>In\u00a0<\/span>props.gs<\/code>, add the email ID of the calendar you want to pull data from. You can adjust the date range by modifying the\u00a0<\/span>start_date<\/code>\u00a0<\/span>in the\u00a0<\/span>code.gs<\/code>\u00a0<\/span>file.<\/p>\n
Execute the script:<\/strong>\u00a0<\/span>You might be prompted to authorize the script to access your calendar and spreadsheet. After running the script, your Google Sheet should populate with the event data from the specified Google Calendar.<\/p>\n
By following these steps, you can easily automate the process of pulling calendar data into your Google Sheets, making it easier to manage and analyze your schedule.<\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
This post was originally published in our community forum. Objective :\u00a0This guide demonstrates how to use Google Apps Script to pull data from Google Calendar and display them in a Google Sheet. This process allows you to automate the retrieval of calendar events and manage them directly within a spreadsheet. Create a New Google Sheet:\u00a0Open […]<\/p>\n","protected":false},"author":48,"featured_media":44470,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[],"class_list":["post-52636","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/52636","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/48"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=52636"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/52636\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/44470"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=52636"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=52636"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=52636"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}