Integrate your SaaS with the tools your customers value
Go to Albato Embedded

Rules for transferring data to Google Sheets

Rules for transferring data to Google Sheets
Google Sheets
·
1/26/2022
·
3 min. read

In order to transfer data to Google Sheets certain rules have to be followed, otherwise correct transfer of data is not guaranteed. Data may be trickled over, placed in the top row, or not transferred at all.

In the sheet where data is transferred, it is forbidden:

  • Using filters on columns.
  • Using formulas in column A or others where data is transferred. If columns A to D are being filled in, formulas can be used in row E.
  • Hide rows or columns.
  • Rename the sheet. If renamed, the automation will continue to work only after updating the data in the connection.
  • Specify punctuation marks in the sheet name, the name must consist strictly of letters and / or numbers.
  • To protect a specific range of cells, that is, to disable editing.
  • Delete the entire row that has already been filled in using Albato. If you want to delete it, fill in the cells with dashes.
  • Delete headers — 1st line. You can rename it, but if you delete column A in the 1st row, the data will stop coming in.
  • Fill in any row yourself, except for the last free one. This means that if you have rows 1 through 20 filled in, you can only fill in row 21 yourself. If you fill in the 22nd and beyond, stable data transmission will stop.
  • Transfer data to a single sheet using different bundles. For each source, you need to make your own sheet.

Also follow the rules of layout:

  • Before creating an automation, you must fill in the 1st row yourself, that is, make a «header».
  • Column A should be filled out, always write in the column A header never leave it empty. In particular, in the automation itself, column A must be filled in.
  • If there is already some data in the sheet, all rows must be filled in. It is not allowed to fill in rows from 1st to 5th, then from 7th to 10th. Row 6 cannot remain empty.

To transfer data, you must have a purely «technical sheet». It should look something like this:

Снимок экрана 2022-04-25 в 14.13.54.png

This option is not allowed:

Снимок экрана 2022-04-25 в 14.16.12.png

If you still need to use filters, formulas, and so on, create two sheets in the table:

  • «Technical» first sheet — Albato sends data to it.
  • In the second sheet, use the formula =IMPORTRANGE.

Thus, you will have one simple sheet where data is transmitted, and a second sheet where all information is duplicated in real time, where you can also configure formulas, filters, and so on. On the second sheet, write this formula under each column in the 1st row. The formula is filled in like this =IMPORTRANGE («table reference»; «sheet name!cell range»).

Example:

=IMPORTRANGE(«https://docs.google.com/spreadsheets/d/1ZKUZNg6OjJOKeAqBLVLc5m-nV6-rJ7″;»Sheet1!A1:A999»)


Google Sheets