Data import from gSheet to SmartSheet via a Connector

Lila80

Hi all, I need support in connecting date from gsheet to smartsheet.

I am having two files:

1. gsheet including rates per employee
2. smartsheet (where I would like to import the individual rates per employee from the gsheet)

I am having one connector in both sheets: individual resource ID (per individual employee)

Which formula would you recommend to use to get the individual rate from the gsheet copied to the smartsheet to the respective individual by using the connector?

Many thanks for your support.

Best wishes,

Lila

jmyzk_cloudsmart_jp

Hi @Lila80

The standard practice for this type of work is to import the Google Sheet first as a sheet and use the INDEX(MATCH or VLOOKUP to get the individual rates per employee.

You can import the Google Sheet manually or use the Datashuttle.

From the context of your question, the connector is a key to retrieve the rates, right? ( connector = individual resource ID )

If so, the formula at the No.2 smartsheet should look like this;

=INDEX({rate range of imported Google sheet},MATCH([individual resource ID]@row, {individual resource ID range of imported Google sheet},0))

You must develop a solution using Smartsheet API or the Bridge to dynamically retrieve the rate data from the Google Sheet without importing.

The solution should have processes like these;

1. Detect change in the smartsheet No.2 and get the individual resource ID using an API webhook or Bridge.
2. Using Google Sheets API, retrieve the rate data for the resource ID.
3. Using Smartsheet API or the Bridge, paste the data to the smartsheet No.2

The Bridge does not have a Google Sheet integration, so you must develop a solution separately.

If the employee rate does not change so frequently, meaning you do not have to retrieve the latest data from the Google Sheet dynamically, I recommend the first approach. (importing to Smartsheet)