Google Sheets is used to export OR import data. This Help Center post explains how to make it possible to export and import using only one Spreadsheet!
1. Install the hook
2. Create a Google Spreadsheet
In order to have your data exported to a specific Google Spreadsheet, you will need to create a new spreadsheet. In this spreadsheet you can create 2 tabs.
The first tab will be used to import data from Google Sheets to MoreApp, and the second tab will receive the data that is exported by MoreApp. Below you can find the following steps.
3. Link the two sheets
The formula to link both sheets is: =importrange("URL-tab2"; "tab 2!A1:D21").
The first part of the formula is the URL of tab 2 which imports data to tab 1. Put this URL between quotation marks in the formula.
The second part of the formula is the name of tab 2 with an exclamation mark behind it. The third part of this formula are the cells that you import from tab 2. Put the name and cells between quotation marks and finish the formula with a bracket. The data from tab 2 will now be imported to tab 1.
Insert this formula in cell B1 of tab 1.
When you inserted the formula, the cell will give an error: #REF, you have to give permission to link 2 tabs. Click on the cell, and click on allow access. Now, the 2 tabs are linked to each other.
4. Add an id-column
If you want to import data to MoreApp, the datasource needs to have an id-column. You can start with 01 in tab 1. This can be done with the TEXT function.
This function converts a number to text according to a specified format, in this case a list of numbers. As seen in the screenshot below, the function consists out of several parts.
- You can start in A1 with inserting id as header.
- Then you can insert the formula in A2. The formula is: =TEXT(ROW(A1); "00")
- The first part of the formula selects the cells that you want to format. Insert ROW(A1); after the first bracket.
- The second part shows the format of the numbers. Fill in ''00" and finish the formula with a bracket.
- This formula will only format cell A1. When you select cell A2 there will appear a blue square, drag this square down to a sufficient amount, for example 28. Now, 28 registrations will have a number.
5. Insert 2 rows
When you export data to Google Sheets from MoreApp, the first two rows of Tab 2 are used for the name of the user and the date when it is filled in. To insert these two rows, right-click on row A, as seen in the picture below. After this, you select 'insert 1 left' to insert a row left of the first row. Do this 2 times so that the name and date of the registration can be visible when you export data to Google Sheets.
After this, change the cells in the importrange formula to C1:D21 to ensure that all the data from tab 2 is copied to tab 1.
If these rows are not inserted and the cells are not changed , the name and date will overlap the information which is exported from MoreApp. The data of the two last rows are then not copied to the first tab.
Now, you have successfully made the Google Spreadsheet in which you can export data from MoreApp and also import data to MoreApp.
6. Add the Google-Sheets-hook to export data
After you have made the Google Spreadsheet ready to export, you can add the Google-Sheets-hook. With this hook, you can export data from MoreApp to Google Spreadsheets. Click here to find out how to do this.
7. Add the Google Spreadsheet as datasource to import data
To import data from the Google Sheets to MoreApp, you can add it as a datasource. Here, you can see how to do it.
You have successfully made a Google Spreadsheet where you can export data to and import data from! Now, you can export your registrations to this Spreadsheet and, at the same, import data from the same Google Spreadsheet!
Do you want to be the first one to see our latest tutorials, go to our YouTube Channel and hit the Subscribe button!