Importing events from the calendar causes disarray in other data columns when sorted by date

I have a unique code that successfully imports my shared Google Calendar into a spreadsheet. In my medical office, I manage all appointments through a master Calendar. The calendar data includes start time, location, description, and title in columns B, C, D, and E. I have added additional columns F to O with data such as 'no show', time of arrival, rescheduled appointments, and formulas to calculate the days between appointments.
However, when the calendar events update in the sheet, the added columns F to O no longer align with the correct row of data. This means that the data I entered for 'no show' or other details end up in the wrong cell. How can I ensure that new rows of data are added to the bottom of the sheet and remain in the correct order to avoid mixed-up data?

function importCalendar(){ 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Calendar Import'); 
  var calendarName = sheet.getRange('C2').getValue(); 
  var start = sheet.getRange('C3').getValue(); 
  var end = sheet.getRange('C4').getValue(); 
   
  var calendar = CalendarApp.getCalendarById(calendarName); 
  if(calendar) {var calendarId = calendar.getId();} 
  if(!calendar) { 
    var calendar = CalendarApp.getCalendarsByName(calendarName)[0]; 
    var calendarId = calendarName; 
  } 
   
  var events = calendar.getEvents(start, end); 
  var eventDetails = []; 
  for(var i = 0; i<events.length; i++){ 
    eventDetails.push([events[i].getStartTime(), events[i].getTitle(), events[i].getDescription(), events[i].getLocation()]); 
  } 
   
  //write calendar details to spreadsheet 
  var startRow = 8; 
  var startCol = 2;  
  for(var j = 0; j<eventDetails.length; j++){ 
    var tempRange = sheet.getRange(startRow+j, startCol, 1, 4); 
    var eventArray = new Array(eventDetails[j]); 
    tempRange.setValues(eventArray); 
  } 
  return eventDetails; 
}

Answer №1

Do you want to know how to ensure that new data rows are always added to the end of your spreadsheet? That seems to be your question.

Currently, your code sets startRow = 8, which means you're consistently starting from that row and replacing any existing data. Instead, consider using the getLastRow() method to find the position of the last non-empty row.

var startRow = sheet.getLastRow() + 1; // Start after the last row

It's typically recommended to use batch operations. While it may not always be necessary, it could be beneficial in your scenario. Instead of calling getRange() and setValues() multiple times, you can streamline your code by only calling them once.

Another option is to utilize appendRow(), but considering your specific case of not printing in column A and the lack of batch operations, it may not be the most efficient choice.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

How to stop Mouseenter event from bubbling up in an unordered list of hyperlinks using Vue 3

I've experimented with various methods to prevent event bubbling on the mouseenter event, but I'm still encountering an issue. When I hover over a link, the event is triggered for all the links as if they were all being hovered over simultaneousl ...

Ways to retrieve the response object from an express application

I am currently working on developing a nodejs application with mysql and my objective is to have my controllers and messages separated into different files. Below are examples of what I am aiming for: First, here is a snippet from my auth controller file: ...

Twice the calls are being made by jQuery Ajax

I am using an <input> tag with the following attributes: <input type="button" id="btnSave2" value="Save" onclick="Event(1)" class="btn btn-primary col-lg-12" /> In addition, I have a script as ...

Issue with Github actions: Failure in mark-compacts process due to ineffective operation near heap limit causing allocation failure - running out of memory in

Whenever I try to build my library files, I keep encountering an out of memory error. In my local setup, I was able to resolve this problem by executing the command export NODE_OPTIONS="--max-old-space-size=8192". Unfortunately, no matter how mu ...

How can you restrict a textbox input to accept only numerical values and decimals using ng-pattern?

I'm working on a code that needs to accept both decimals and integers. How can I verify this using the ng-pattern? An example of the code: Some test cases include: 1) 22 = Should pass, 2) 22.5 = Should pass, 3) 2a = Should fail, 4) @#@ = Should ...

Retrieve information from the input field and then, upon clicking the submit button, display the data in the

When a user inputs their name, email, subject, text, and telephone number, I want to send an email with that data. The email will be sent to a specific email address, such as [email protected]. This process is crucial for a hotel website, where the em ...

Display custom modals in React that showcase content for a brief moment before the page refreshes

I recently developed a custom modal in React. When the Open button is clicked, showModal is set to true and the modal display changes to block. Conversely, clicking the Close button sets the display to none. However, I noticed a bug where upon refreshing ...

If a single checkbox is selected within a group, then every other checkbox should be deactivated and deselected

Here is the code snippet provided: function listen(element, event, callback) { if (element.attachEvent) { element.attachEvent('on' + event, callback); } else { element.addEventListener(event, callback); } } var form = document.que ...

AngularJS testing typically involves the use of the inject module injection process

Currently, I am working on testing a service called documentViewer, which relies on another service named authService. angular .module('someModule') .service('documentViewer', DocumentViewer); /* @ngInject */ function Do ...

Displaying Dynamic Content in React Table Rows Based on Conditions

I'm populating a table with multiple rows using props. If a returned prop is an empty string "" , I want to exclude that row from rendering. <Table.Body> <Table.Row> <Table.Cell>Producer</Table.Cell> ...

Preventing click propagation for custom react components nested within a MapContainer

I have developed a custom control React component for a map as shown below: export const MapZoom = () => { const map = useMap() const handleButtonClick = () => { map.zoomIn() } return ( <IconButton aria ...

Having trouble limiting the number of special characters in AngularJS

I am having trouble restricting special characters and spaces in the input text field. I tried using the following code snippet: ng-pattern="/^(?=.*[0-9])(?=.*[a-zA-Z])([a-zA-Z0-9]+)$/" to prevent special characters, but it doesn't seem to be workin ...

Reorganize the 2D array to switch the column values to row values while keeping the initial keys intact (transpose while maintaining the top-level

In this scenario, there is a parent array structured like so: $parent = [ 1 => ['test1', 'test2'], 2 => ['test1_1', 'test2_2'], ]; The goal here is to group the data by column. Intended output: [ ...

The Vuex mutation does not execute synchronously and does not resolve as a promise

My vuex mutation doesn't work synchronously as expected. Here is the code: mutations: { computeStatusData(state, status) { if (status.active !== true) { return } const started = new Date(status.startedAt); started.setHour ...

Exploring Collection Using Mongodb Search

I am looking to search a collection in MongoDB that includes: 'food_name' => 'fish' as well as 'room_features' => array ( 0 => 'Shower', 1 => 'Hairdryer', ), I have attempted t ...

When trying to make a POST request, the browser displayed an error message stating "net::ERR_CONNECTION

Currently, my project involves coding with React.js on the client side and Express.js on the server side. I have encountered an issue when attempting to use the POST method to transmit data from the client to the server for storage in a JSON file. The erro ...

Unlocking Discord Account Information through OAuth2

Currently, I am in the process of developing a moderation bot for Discord. I am working on implementing a paid plan and as part of that, I require users to log in with their Discord account using OAuth2. This allows me to retrieve user data and identify wh ...

The submit function in Jquery is not functioning properly within the success callback of an Ajax request

After successfully submitting a form in AJAX using POST, I receive a new form that needs to be automatically submitted in jQuery. However, for some reason, the .submit() function seems to be ignored and I can't figure out why. I've tried adding ...

Tips for preserving the data type of a Java Array within a Map<String, Object> while using GSON's toJson and fromJson methods

In my program, I am sending JSON data through a serial port and receiving it back as well. To avoid sending the complete list of valid keys every time, I have implemented a method where the client sends requests using a Map structure like this: HashMap< ...

Passing an object in an ajax call to a function: a comprehensive guide

@model IEnumerable<HitecPoint.BlackBox.Models.SMSReportModal> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"> </script> <script type="text/javascript"> var MyAppUrlSettin ...