In this post we’re going to look at how we can get data from an Excel spreadsheet and import some of it into a Google Sheet. As an example, we’re going to upload an Excel which contains the current month’s royalty data from Amazon and add it to a central sheet which contains all the previous month’s royalties.
I use this script to add my book royalties to a central file, so that it can be declared every quarter in my tax declaration. They don’t need all the details that are in the original download from Amazon, so I remove certain columns before adding the data. All the data is fictitious and wishful thinking! 🙂
This of course can easily be adapted to your own needs, the main idea here is how we can easily get data out of an uploaded Excel and add it into a Google Sheet.
The overall process
We have a folder where the royalty Excel is uploaded to and where the main royalties Google Sheet is, which contains all the royalties from previous months.
The excel contains this data and the red columns are the ones we don’t need. The script will get this data, add it into a newly-created Google Sheet, and then remove and move the columns.
On the main royalty sheet, we have the previous month’s data, and as you can see we only have 8 column containing the data the tax office needs.
We run the script from the royalties file from the menu.
This inserts the latest data at the top of the sheet. For example, here it’s added March’s above February’s.
OK, let’s look at the code and see how this is all done.
The code
Get the spreadsheet and folders
1. function addLatestRoyalties() {
2. //Get folders and current SS
3. const ss = SpreadsheetApp.getActiveSpreadsheet(),
4. fRoyalties = DriveApp.getFolderById('1Cry-cfbP0ZYTDmTDHz248rWkDRM_7-ec'),
5. fArchive = DriveApp.getFolderById('1-NQpxTVkE6IEo7NnhMvdNxa1IhAPtP6V'),
6. shRoyalties = ss.getSheetByName('ROYALTIES');Line 1: Set up the function.
L3: Get the active spreadsheet.
L4-5: Get the Royalties folder, where the Excel file will be uploaded, and the Archive folder, where the Excel file will be moved to once imported to the main sheet.
L6: Get the sheet called ROYALTIES.
Get the Excel files in the folder
8. //Get Excel files in folder and check if there is one there
9. //and if so loop thru folder
10. const files = fRoyalties.getFilesByType(MimeType.MICROSOFT_EXCEL);
11. if (files.hasNext()) {L10: Get all the Excel files in the Royalties folder.
L11: Check to see if there are any Excel files in the folder. If so, it will run the code below, if not an alert will be shown.
Loop through the files
13. while (files.hasNext()) {
14. let excelFile = files.next();
15. let excelFileId = excelFile.getId();
16. excelFile = DriveApp.getFileById(excelFileId);L13: Loop through the Excel files.
L14: Get the next file in the iterator.
L15: Get the Excel file’s ID.
L16: Get the Excel file by its ID.
Create a new Google Sheet
18. //Make new Google Sheet with Excel data - in My Drive
19. const blob = excelFile.getBlob();
20. const resource = {
21. title: excelFile.getName().replace(/.xls?/, ""),
22. key: excelFileId
23. };
24. const newTempGS = Drive.Files.insert(resource, blob, {
25. convert: true
26. });L19: Get the Excel file’s blob.
L20: Set up the resource object for the file.
L21: Get the Excel filename and remove the .xls at the end. (Optional)
L22: Set the key to the Excel file ID.
L23: Close the resource object.
L24: Create a new Google Sheet from the data from the original Excel file. Note, this uses the Drive API, which needs to be turned on (see further down).
L25: Set the property convert to true.
L26: Close insert.
Format the sheet – Remove & move columns
28. //Delete columns and move Date column
29. const ssNew = SpreadsheetApp.openById(newTempGS.id);
30. const shPayments = ssNew.getSheets()[0];
31. const columns = [17, 15, 11, 10, 9, 7, 5, 2, 1];
32. columns.forEach((col) => {
33. shPayments.deleteColumn(col);
34. });
35.
36. shPayments.moveColumns(shPayments.getRange("C1"), 1);L29: Open the newly-created Google Sheet by its ID.
L30: Get the first sheet, which will have the royalty data on it.
L31: I want to remove certain columns and I can state those in an array, then loop through them. Note, these start from columns to the right and work towards the left. This is because if we started on the left, the columns on the right would change reference as they are being deleted.
L32: Loop through the columns array.
L33: In each loop delete the corresponding column.
L34: Close the forEach loop.
L36: I also want to move the date column, currently in column C to the first column, A.
Add data to main sheet
38. //Get data & rows
39. let newPayments = shPayments.getDataRange().getValues();
40. newPayments.shift();
41. const numOfRows = newPayments.length;
42.
43. //Insert row into this sheet
44. shRoyalties.insertRowsBefore(2, numOfRows);
45. shRoyalties.getRange(2, 1, numOfRows, newPayments[0].length)
46. .setValues(newPayments);L39: Get all the data on the Payments sheet on the new Google Sheet.
L40: Remove the header row.
L41: Get the number of rows of data by getting the above array’s length.
L44: Now, we add it to the royalties sheet. I want the latest data at the top of the sheet, so we use insertRowsBefore to insert it before the current data.
L45-46: Add the data to the sheet.
Delete the sheet and move the Excel
48. //Delete temp GS and Excel to Archive folder
49. DriveApp.getFileById(newTempGS.id).setTrashed(true);
50. excelFile.moveTo(fArchive);
51. }
52. }
53. else {
54. SpreadsheetApp.getUi().alert("No Excel file found in folder.");
55. }
56. }L49: Delete the Google Sheet that was temporarily made.
L50: Move the original Excel file to the archive folder. You of course, may wish to just delete it.
L51-52: Close the while loop and the if statement.
L53: If we don’t have any Excel files in the folder, it will throw an error, so let’s inform the user that there isn’t one. Set up the else statement.
L54: Show the alert.
L55-56: Close the else statement and function.
Add menu to run the script from
58. function onOpen() {
59. SpreadsheetApp.getUi().createMenu('Royalties')
60. .addItem('Add Royalties', 'addLatestRoyalties')
61. .addToUi();
62. }L58: I want to run this from a menu, so let’s set that up. Set up the onOpen trigger.
L59: Create the menu called Royalties.
L60-61: Add the menu item Add Royalties and add it to the Spreadsheet UI.
L62: Close the function.
Turning on the Drive API
Before running the script, we need to turn on the Drive API.
Click on the plus icon next to Services in the Script Editor.
Select Drive API and click Add.
This adds the Drive API as a service.
Run the script and you will see that the data from the Excel has magically been formatted and added to the Google Sheet at the top of the sheet.
Every month all I need to do is download the Excel from the Amazon site and upload it to the royalties folder, then run the script from the royalties spreadsheet. You could, of course, add a trigger which does the last part automatically whenever there’s an Excel in the folder.
Files
Here is the folder which contains the Google Sheet with the above code. To practise using it, make a copy of the Google Sheet and the Excel file, then move those to your own folder and change the folder IDs in the script.
I’ve also added a pivot table on a separate sheet which will show the royalty totals per month.
You can easily adapt this to suit your needs and it shows how easy it is to get data from Excels and add them into Google Sheets.
Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).
a
