API Connector Documentation
Set Data Destination
This article will describe how to set a destination sheet and cell in Google Sheets, using the API Connector add-on for Sheets.
Contents
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
How to Set a Destination
API Connector will print data into your sheet starting from the destination Sheet and cell you specify. There are 2 methods of setting a destination cell: manual entry and automatic.
Manual Entry
If you manually enter a Sheet name, it will use that sheet. If that sheet doesn't exist yet, it will be created when you click Run. By default it will print to cell A1, or you can manually enter a destination cell.
Automatic
If you click Set Current, it will automatically set your data destination to the currently focused cell in your sheet.
Keep Formulas
By default, all data will be cleared to the right and below the starting cell.
If you are including calculations and formulas, we recommend adding them into columns on the left. Then you can set a destination cell to the right of those formulas, such that they don't get overwritten.
If you would prefer not to clear data in your sheet at all, you can toggle this off under Output options:
This allows you to send the response from multiple requests into the same sheet, or update API data without changing any existing data in your sheet.
Overwrite vs Append Mode
When you choose Overwrite mode, the data response will begin from the cell you specify. When you choose Append mode, it will begin from the first empty row. Either way, it will use the column specified in your starting cell input field.
it is possible to have a dynamic cell address with a formula? exemple : I want to put data from a request under another request. If the first request have 101 lines, I want the second request to be located from line 102 on the same page.
If you use append mode, it will automatically place each set of results below the current set of results. Does that get you what you're looking for?
Is it possible to only clear below the starting cell and not to the right?
Not currently, but we plan to add an option for that in the next few months so that people can run API requests onto the same sheet.
ok thank you is there any workaround for now? i was thinking to use importapi but that one i can't schedule weekly?
There's no workaround per se, you would need to send data into different sheets. But you can hide those sheets and pull the data you need from each sheet into a summary sheet using Sheets formulas like QUERY, if that works for you.
Update: We've added an option to not clear data in sheet after successful runs.
can I specify a formula for destination sheet. for example, to create a new tab with the current date or a random number.
There's no functionality for that, but thank you for the idea, we'll consider it in the future.