What you see on the screen is Google Sheets, which functions similarly to Excel. Since it operates in the cloud and requires an internet connection, it has some unique advantages. Nowadays, we often use this tool. For example, we can work on something in Google Sheets and then link that information to Excel. While we can certainly save it as an Excel file and work on it there, if any changes occur in Google Sheets, we will need to update our work in Excel as well. Therefore, we can use Google Sheets as a sort of database, keeping it as the original source while connecting it to Excel. This way, we can reflect any changes in real time. Let's take a look at how to do this. Let's assume we have some data entered here; there may be additional entries, or values may change due to formula adjustments.

To maintain this, we first need to share the information. Instead of using the sharing option here, if you look at the file, you will see an option called Share. This may vary slightly depending on the version of Google Sheets you are using. If you look here, there is a Publish to the web option. If you click on this, you will have the option to publish the entire document. If there are multiple sheets, you can select them individually or choose to publish them all. For now, let's select only Sheet 1. If the data is organized as a dataset rather than a form, do not save it as a webpage. Instead, use the comma-separated values format. Of course, there is also an option for tab-separated values, so you could use that as well, but it is better to use the comma-separated values format. So, let's set it to comma-separated values. Now, let's expand this. When you publish the content here, what conditions will you set for the publication?

If you do this, it will be fine. Once the document is modified, the publication will automatically start. So, when you work here and some changes occur, that content will be published automatically. Usually, it doesn't get published in real-time; there's a gap of about five minutes. Therefore, if there are changes here, and you have linked Excel, it won't be reflected immediately in Excel, and you will have to wait. Sometimes it can be updated within five minutes, but it can also take a bit longer, usually around five minutes. 

Now, once you are in this state, you click on Publish. It will ask you, Do you want to publish? If you click Confirm, a link will be created. You can copy it by pressing Control + C. Now that it is copied, you go to Excel. In Excel, there are various menus, and you go into Data. Although it was in CSV, select Web. After selecting Web, you will see a URL field.

I have pasted the previously copied content, so I will click on Confirm. Then, the data comes in like this, separated by commas and if it's tab-separated, I would use the tab. In the case of TSV, it has tabs, while this one is comma-separated. After that, let's click on Load. The data comes in as it is. Now, let's go to Google and make a change. I'll say that one more has been added. So, one has been added, and another one has been added. In Google, there is no separate save button. When I make changes, it saves automatically, so there's no need to look for a save button. You can see that it says Modified a few seconds ago. That's how it works. Now, looking at Excel, if I open this file or import it, I would need to click the refresh button. But right now, I'm continuing.

Now, even if I press it, there is no response. Okay, it has come in now. This one came in relatively quickly. For number 6, it automatically comes in like this. This means that we can connect it and use it in this way. In the case of the sample we just did, if the data is well organized into a dataset, it's better to save it as something like a CSV and retrieve it. Sometimes, it can be in a form like this. Let's see how we can handle this kind of situation. This is not exactly a well-organized database. In such cases, although it's not very intuitive, it’s better to do it this way. When posting to the web, this time we are using Sheet 2. So, for Sheet 2, we will simply create a webpage. We click on 'Publish' and a link is generated just like that. Let's press Control C. Now, let's go to Excel and in a new sheet, we will click here to input the data, select 'Web', and then paste the link we copied earlier.

I will attach the URL, so let me click on the confirmation. Then, it looks a bit different from earlier; it comes out in this shape. This retrieves everything. Here, there was a timetable, and there was this layout. There was an empty space here, and here, rows 1, 3, and 4 are the row numbers of the sheet. It retrieves all of this. I will try to do a node with this shape first. So, it retrieves it just like this. This is the timetable data we want. There is content here, and now there are column numbers, and below the column numbers, it retrieves everything from the entire database. Now, let's say I input 7 here. Let's assume one more has been added. I will copy this and create a format here. In this case, the yellow area has the font bolded, but these things are all meaningless since it retrieves only the data.

I will only be able to retrieve the content. First, I added one, and now let's take a look at Excel. Here, we should see number 7 appear. Let's refresh this. Alright, number 7 has come in. I added some time, but this process takes a bit of time, right? It takes about 5 minutes to reflect changes, so we need to wait a bit. If you want to check whether the changes have been reflected or if the previous data has not changed, it’s good to use a trick in such cases. For instance, you can insert something that shows the current time in the original data, like now. By doing this, you can display the current time. When the time changes later, you can verify it. Right now, it shows 9:57.

If the current time is saved, let's try refreshing it once. The data has come in. Looking at the time, I can see when this data was recorded. I merged the cells, so when it comes into Excel, it appears as two separate entries. It might be good to keep this in mind. 

Next, I kept pressing refresh, but if that’s cumbersome, you can go into the properties of the refresh. If you select refresh at this interval, you can set it to automatically refresh the data from Google at regular time intervals. You just need to check that box. Additionally, if you want it to refresh every time you open the file, you can check that option as well. By doing this, the benefits of automation will be enhanced. 

Anyway, Google has different functions compared to Excel, meaning there are functions available in Excel that may also exist in Google.

There are functions or features that are only used in Google. If you have to use Google for that reason, you can work on it in Google and then link the results to Excel. When you work with these features, it is important to note that this does not reflect in real-time; it requires some time, about five minutes. Therefore, it will be necessary to carefully plan the order of tasks considering this point.

구글 시트를 엑셀에 실시간 연동하기

Computer and Network Tips
박철우 / Chulwoo Park