SEC | S20W6 | Data Analysis with Google Sheets: (Importing and Exporting Data: Convert Excel to Google Sheets Etc)

aneukpineung78 (70)in #spreadsheet-s20w6 • 5 days ago

This is my homework post for Steemit Engagement Challenge Season 20 Week 6 assignment of Professor @josepha’s class, SEC | S20W6 | Data Analysis with Google Sheets: (Importing and Exporting Data: Convert Excel to Google Sheets Etc).

Note :

I performed this task on Windows 10 PC, Google Chrome.

Task 1 - What do you understand by importing and exporting data in Google Sheets? Use the Coefficient method and input the Excel Work that you did in previous lesson 2 into a new Google sheet and shared with us all the necessary procedures. ^{Note: Should in case you didn't participate in the previous lesson 2, you can upload any Excel work that you have.}

1.a. My Understanding on Importing and Exporting Data in Google Sheets

Google Sheets as part of Google Workspace offers features that can help spreadsheet application users, especially since it is a web-based application. Some of its advantages are: 1) automatic synchronization across devices; 2) realtime collaboration with other users who are given access; 3) easily accessible anywhere as long as there is an internet connection; 4) automatic saving; 5) because it is part of Google Workspace, it is easy to manage data across applications, and so on.

There are times when users have spreadsheet files in an offline application (e.g. MS Excel) and because of a need they need to have the file in Google Sheets, perhaps because of the need for collaboration with colleagues who happen to be in a distant place (e.g. three Steemians who run a joint community curator program, each of them is based in Indonesia, Nigeria, and Venezuela). Files that were previously managed by one of them offline, one day became important to be reviewed by two other team members in real time. This is where Google Sheets comes in to fill that space. And therefore the ability to import spreadsheet files from elsewhere (hardware or cloud storage ) and in other forms (created with other applications) is important, and Google Sheets has this ability.

And there are also times when people want to save files from Google Sheets in offline form to be managed with applications such as MS. Excel or WPS Office Spreadsheet), so the ability of Google Sheets to save in other forms is very important. There are 6 types of export documents that Google Sheets can provide: MS Excel (.xlsx), OpenDocuments (.ods), PDF (.pdf), Webpage (.html), Comma Separated Values (.csv), and Tab Separated Values (.tsv). Quite a lot of options and it can be said that they cover all the needs related to spreadsheet management.

And the interesting news is that Google Sheets has both capabilities (the ability to import data from other types of spreadsheet files and export to other types of spreadsheet files), so users don't have to worry that they will be constrained because of this kind of need.

Another advantage of Google Workspace environments is their ability to collaborate with various add-ons that can support user performance. For example, in terms of importing data, there is an application called Coefficient that acts as an add-on in Google Sheets.

1.b. Installing Coefficient

Open Google Sheets, click the [Extentions] tab, in the dropdown menu click [Add-ons], then select [Get add-ons].
^{Image is clickable and might show larger resolution.}
Type in the search bar, then select Coefficient in the search results dropdown.
^{Image is clickable and might show larger resolution.}
In the results list, select Coefficient.
^{Image is clickable and might show larger resolution.}
On the next page click [Install].
^{Image is clickable and might show larger resolution.}
Next, authorize the installation by pressing [Continue].
^{Image is clickable and might show larger resolution.}
Sign in with Google by providing an email address.
^{Image is clickable and might show larger resolution.}
After specifying the email address used to sign in to Coefficient, on the next page press [Continue]. Make sure you have read and agree with the statements above.
^{Image is clickable and might show larger resolution.}
On the next page you should allow Coefficient to do a few things, including edit and delete data in Google Sheets, and even leave it running even if you're not running it. If you agree and understand, press [Allow].
^{Image is clickable and might show larger resolution.}
A moment later the screen will show a message that Google Sheets and Coefficient have successfully connected. Press [Done] to finish the process.
^{Image is clickable and might show larger resolution.}
Press the [X] button to close the dialogue box.
^{Image is clickable and might show larger resolution.}
Before being able to use Coefficient, users are asked to fill out some kind of data form, it looks like Coefficient collects the user's background data.
^{Image is clickable and might show larger resolution.}
Now we have Coefficient in Google Sheets as an add-on, and we can use it anytime. Next we will see how to use this add-on.

1.c. Importing MS Excel Files into Google Sheets

Click [Extensions], now we can see in the dropdown menu that appears there is Coefficient, click on it, and then select [Launch].
^{Image is clickable and might show larger resolution.}
The Coefficient dialog box then appears as a sidebar on the right of the screen. Select [Import from...].
^{Image is clickable and might show larger resolution.}
Users can select the relevant one which will direct them to their desired import type.
^{Image is clickable and might show larger resolution.}
Select Excel.
^{Image is clickable and might show larger resolution.}
As I found out, there was a problem with connectivity with Google Drive, and One Drive has been the solution. So please make sure that the Excel file you want to import has been uploaded to One Drive. In the side bar of Coefficient, choose [From One Drive].
^{Image is clickable and might show larger resolution.}
In the next window, give Coefficient permission to retrieve information in One Drive by pressing [Accept].
^{Image is clickable and might show larger resolution.}
Locate and select the file you want to import into Google Sheets, then press [Select].
^{Image is clickable and might show larger resolution.}
Perform the necessary configuration to determine which tabs (sheets) to import, as Coefficient cannot import the entire workbook in one go. Also set the range (if you want to import the entire sheet, select [Entire Tab]) and Header. Press [Next] to continue.
^{Image is clickable and might show larger resolution.}
Perform further configuration including naming the tabs (otherwise the name will match the name of the original file). 10. In this example I name it “Sheet 1”. To execute, click [Import].
^{Image is clickable and might show larger resolution.}
Once the import process is complete, the sidebar will display a success message. Here users can set the frequency of data refresh, meaning that if there are changes in the data in the original file, Google Sheets will also follow. As seen in the image below, I already have a new sheet (tab) in my Google Sheets, named “Sheet1 (1)”. Because I named it with the same name as the existing tab, Google Sheet modifies the name of this imported tab.
^{Image is clickable and might show larger resolution.}
I repeat the tab import process with Coefficient to import another tab from the same workbook. Then I delete the empty tab and rename the rest. And here's what I got, there are 2 tabs (Students' Grade and Result):
^{Image is clickable and might show larger resolution.}

1.d. The Limitation of Importing MS Excel Files into Google Sheets Using Coefficient

Coefficient helps users to import tabs (sheets) from other files from the cloud drive or the hard drive, but through my experience, I think this kind of add-on should give users more opportunities to choose more tabs, instead of one tab (sheet) each time. Imagine if someone wanted to import 10 tabs from an MS Excel file and they had to do it one by one, it would be very time-consuming.

Task 2 - Share with us the needed procedures for converting Google Sheets Files to Excel File using the Google Sheet that You created in Lesson 4 and detail out the limitations that you observe while importing and exporting your data.

2.a. Converting Google Sheets Files to Excel File

Click [File] in the menu bar.
^{Image is clickable and might show larger resolution.}
Browse storage and find a place to save. Change the file name if necessary. Click [Save].
^{Image is clickable and might show larger resolution.}
That's it, now we see the file has been downloaded successfully.
^{Image is clickable and might show larger resolution.}

2.b. The Limitations of Exporting Data From Google Sheets Files to Excel File

In saving the Google Sheets Workbook to MS Excel, I did not experience any problems at all, it can be done in a few simple steps that are very easy. However, limitations may arise not in the export process but in the management of the exported file. This is because even though Google Sheets is similar to MS Excel in outline, they also have many differences. Based on my searches on the internet, I found the following to be limitations that the exported file might have:

Differences in formulas. There are several formulas in Google Sheets that are not recognized by MS Excel and there is no replacement, for example GOOGLEFINANCE, ARRAYFORMULA, and IMPORTRANGE.
Visual display. Some visual elements may look different, because some Google Sheets visual formats are not supported by Excel.
Differences in interface and function implementation can also make features in Google Sheets not fully compatible with Excel.
Collaboration is lost. All collaboration activities such as comments or chats that have been created in Google Sheets will be lost when the file is saved as an Excel file (.xlsx).
Real-time data capture function will be lost. Google Sheets has the ability to automatically collect real time data through the use of APIs or automatic external data imports, this is supported by its nature as a web-based application, which of course Excel does not have.
External cell references contained in Google Sheets files will no longer work and users will have to fix them again manually if they still need these references.
And so on.

In essence, the difference in environment, interface, functions, formulas, external data processing capabilities, add-ons, and so on, will become a problem when a complex Google Sheets file is saved as an Excel file. So, while Google Sheets files can be saved as Excel files, it doesn't necessarily mean things will be that easy. With simple files, there might not be any significant problems.

Task 3 - Between the use of Google sheet and Excel spreadsheet which do you find more user-friendly and acquainted to?

In my experience, Google Sheets and MS Excel are both easy to use. That is, I have no difficulty in running these two programs. The obstacle will occur in using Google Sheets when we have problems in internet connection. For the operation itself, I don't feel like I'm facing any major difficulties. Both applications have an interface that is easy for me to understand, and because they basically have the same working system.

The use of these two programs is also of course greatly influenced by the nature of the workbook we are working on. If we want real time collaboration with distant coworkers, then Google Sheets is the choice. But in the case of personal financial records, for example, regular Excel is a more reasonable choice. Likewise, for example, if we run a spreadsheet with the need for real time data collection using API technology, then Google Sheets is the answer. That is, as a web-based application, Google Sheets offers wide possibilities in web-based operations that MS Excel cannot offer.

I personally work more often with MS Excel compared to Google Sheets, not because I feel more comfortable with Excel, but because I think MS Excel has everything I need and I don't need web-based features more often. One of the jobs I'm working on using Google Sheets is a project I'm doing with a Steemian where we're monitoring the activities of members in a community, and recording in Google Sheets, where we're also generating visualizations in the form of charts.