Integrate with Microsoft Excel
Connect and sync your spreadsheets from Google Sheets to AppAlloy to make a powerful app that turns your work with data become more easy and intuitive.
Overview
Microsoft Excel is a powerful app for working with data. By integrating it with AppAlloy, you can make the data work more intuitive and convenient for your team.
This synchronization is perfect for scenarios where you already have your database stored with Excel Spreadsheet and the frequent updates are crucial to your team, such as:
Sales Teams: Seamlessly manage customer data, ensuring contact details and sales figures are always current.
Educational Institutions: Effortlessly track student performance and attendance, with instant updates for teachers and administrative staff.
How it works
The integration between AppAlloy and your Microsoft Excel will require you to provide AppAlloy full set of permissions to edit your sheet.
Once the integration is enabled, you can choose which spreadsheets or sheets to sync to AppAlloy. You can get a view of your data table right in the Data tab of AppAlloy.
With your data synced to AppAlloy, you can generate intuitive views on records of every and all rows in your sheet. In addition, you can use the app to add new data rows, and edit or delete the existing ones.
All changes made in the app are updated in real-time in your online Microsoft Excel file, and vice versa.
How to set it up
To connect and sync data between an online spreadsheet with your app:
Go to tab Data, on the section All Tables, click the + icon
Choose Sync with Microsoft Excel
Click Choose File...
Choose an Excel file from your One Drive.
Select the checkbox of the sheet(s) you would like to sync
Select the check box Automatically create new pages for selected sheets if you want AppAlloy to create a new page to view records from the sheets with an intuitive collection layout.
Click Save to start syncing
Manage table
Change table name
By default, the table has the same name as your original sheet. To change its name:
In the table view, click on the table name
In the Name field, enter a new name for your table,
Click Save
Manage table accessibility
In the Data tab, you have full control over the accessibility to the data in a table for your users. Click on the table name to open the accessibility control board.
For your team members, you control if they can:
Add new data
Edit data
Delete data
For public users, in case you share the Mobile Web app link with public sharing, you allow them to:
View the data
Add new data
Edit data
Delete data
This configuration will affect the way users can access and interact with data while using the app.
Click Save to save all of your updates.
Adjust column size
Within the data tab of AppAlloy, you also can adjust the column size for your convenience view:
hover your cursor on to the vertical line between two column headings until you see it turn into the col-resize style
click and hold on while dragging it right or left to change the column size.
Change column order
To manage the horizontal sorting of columns in a table:
Hold your cursor on a column heading
Drag the right or left to change the column position within the table.
Changing column order in AppAlloy does not change the column order in the original datasheet.
Delete the table
To delete a table from the data tab of AppAlloy:
In the table view, click on the table name
Choose Rename
Enter a new name for your table, and click Delete
Deleting a table in AppAlloy will not delete the sheet in the Excel file but stop its synchronizations.
Managing the data
Once the setup is completed, you can get a table of your sheet within the data tab of AppAlloy like the one as below.
The synchronization does not stop here. Every change to your data made in your sheets is updated to AppAlloy in real-time, keeping your app always up-to-date.
Add new row
From your sheets, to add a new row:
Go to any empty row
Add new data to its cell.
The new row will be added to the table of your data in AppAlloy as well.
Add new column
In your sheets, to add a new column:
Go to any empty column.
Give the column a heading.
The new column will added to the table of your data in AppAlloy as well.
Manage data type
For the data type, you need to format the data in Google Sheets first.
For string data, we recommend the type "Text".
For numeral data such as price and quantity, we recommend the type "Number".
For date, time, and date & time data, we recommend the type "Date" or "Date time".
Also, AppAlloy will quickly check the heading and first 5 cells of each column and suggest the suitable components to represent the data in the record view and suitable input fields to collect data in the form view.
See all the components of record view and fields of form view available in AppAlloy:
Edit data
In Google Sheets, click on any cell of your sheet to edit its content.
The changes will be reflected in real-time both in AppAlloy and then shown in your app.
Delete data
You can delete data from a cell, a whole row, or a whole column.
In Google Spreadsheet:
to delete a cell, click a cell and press “Backspace” on your keyboard to delete.
to delete a row, choose the whole row, and press “Backspace” to delete.
to delete a column, choose the whole column, and press “Backspace” to delete.
If you just want to remove the column from AppAlloy but not delete it from your sheet, you can delete the column's heading only.
Special note
Access permission
AppAlloy requires permission to edit for the later collaboration between your app and sheets, so you must be the owner or at least have the editor access permission to the sheets that are connected and synced to AppAlloy.
_StarionId column
AppAlloy will add a new column _StarionId containing the unique ID for the integration work between the app and sheets. This column cannot be deleted unless you disable the integration between your sheets and AppAlloy.
Column and row order
AppAlloy will sync randomly the order of columns and rows of your sheets. To check if all rows and columns are synced, you can check by the number of columns and rows.
AppAlloy allows you to change the order of columns of your data sheets in the data table. Just hold your cursor on the column headings for a few seconds then drag it right or left to change the order. However, this change of column will not be saved to your spreadsheet in the Microsoft Excel file.
Troubleshooting issue
A row or column is not synced
Because AppAlloy synced the rows and columns in a random order, please check the number of rows and columns to make sure all data are synced.
Make sure you choose to sync the columns during the steps of setting up the connection between your spreadsheets and AppAlloy. You can delete the table and re-take the steps.
Otherwise, if your column is missing headings, AppAlloy will be unable to detect it as a row to sync it. So, please make sure your column has its heading filled.
Currency is not synced
You may have a sheet that has some columns showing pricing data with the currency symbols. When you sync the sheets to AppAlloy, the symbol is removed from all or some cells of the column.
We recommend you just use the format “Number" for any numeral data. In AppAlloy, we have suitable components to represent your data in the way you want to show your numeral data like numbers with prefixes or currency symbols.
Date and month are confused for date & time data
Date & time data have different formats. Therefore, to make sure AppAlloy recognizes the date format correctly, you must format the column of date & time data with the date & time format.
Last updated