Table of Contents

2-3. [Custom App] Integrating SaaS with Google Sheets

Yamaguchi Kaori Updated by Yamaguchi Kaori

This guide explains how to manage and sync SaaS accounts with custom apps using Google Sheets. In addition to manual entry or CSV imports, you can now manage and sync SaaS accounts directly via Google Sheets.

　　　

Usage

  1. Prerequisites
    Google Sheets and data preparation
    Sharing access permissions
  2. Linking Google Sheets
    Create a new custom app and link a Google Sheet
    Link a Google Sheet to an existing custom app
    Re-link a Google Sheet
  3. Running Sync
    Sync with Google Sheets

　　　　　　　　

Prerequisites

To link Google Sheets, please perform the following preparation steps.

　　　　　　　

Google Sheets and Data Preparation

First, prepare the format of your Google Sheet.

Header Row (Required)

Please ensure that the first row of the sheet contains headers. 

The AI will use these headers as a reference for automatic mapping.

Flexible Formatting

You may use data exported directly from your SaaS. 

The AI will identify items based on the headers.

Setting Up Sharing and Access Permissions

To allow the system to access your Google Sheet, add the dedicated account to the shared members.

  1. Click File in the top-left corner of the Google Sheet > Share > "Share with others".
  2. Paste prod-sheets-reader@money-forward-itmc.iam.gserviceaccount.com into the "Add people and groups" field and click [Send].
  3. When "Share with people?" appears, click [Share anyway].

　　　　　　　　

  1. In Integrations > Integrations, click [Register and manage services without integration].
  2. On the "Register and manage services without integration" screen, configure the following items and click [Create].
    Search for a service：Search for and select a service, or enter a custom service name.
    Workspace name：Enter a preferred workspace name.
    Data source：Select "Google Sheets".
  3. On the "Specify Google Sheets file" screen, enter the "Google Sheets file URL" and click [Start Integration].
    Paste the URL of the Google Sheet you prepared into the "Google Sheets file URL" field.
  4. On the "Column Mapping" screen, check the "Mapping Destination" and "Data Preview," then click [Confirm and Import Data].
    On the "Column Mapping" screen, the AI will automatically set the "Mapping Destination." Please change it if necessary. You can map to "Saas User Id/Display Name/Roles/Licenses/Username/Email/Status/Two FaLast/Activity/Do not map." Please set columns you do not want to import into Admina to "Do not map."
  5. The "Connecting to file" screen will appear.
  6. The "Integration Complete" screen will be displayed, so click [Finish]. 
    You can view the linked custom app on the Services screen.

　　　　　　　

  1. Go to Services > Custom App > Information and click [+New Integration].
  2. Enter a workspace name, select "Google Sheets" as the data source, and click [Create].
  3. On the "Specify Google Sheets file" screen, enter the "Google Sheets file URL" and click [Start Integration]. 
    Paste the URL of the Google Sheet you prepared into the "Google Sheets file URL" field.
  4. On the "Column Mapping" screen, check the "Mapping Destination" and "Data Preview," then click [Confirm and Import Data].
    On the "Column Mapping" screen, the AI will automatically set the "Mapping Destination." Please change it if necessary. You can select the following for Mapping Destination: "Saas User Id/Display Name/Roles/Licenses/Username/Email/Status/Two FaLast/Activity/Do not map." Please set columns you do not want to import into Admina to "Do not map."
  5. The "Connecting to file" screen will be displayed.
  6. The "Integration Complete" screen will be displayed, so click [Finish].
    You can view the linked custom app on the Services screen.

　　

Re-linking Google Sheets

If you need to change the mapping, click the three-dot menu［︙］to the right of the workspace under Information and select [Re-link]. 

After clicking [Re-link], please refer to step ③ and onwards in the "Link a Google Sheet" section.

　　　

Syncing with Google Sheets

If you update the Google Sheet, the account information in the custom app will be updated via automatic or manual synchronization.

  • Automatic Sync：Account information is updated during the nightly automatic sync.
  • Manual Sync：Please refer to the "Execute Sync" section for how to perform a manual sync.

　　　

FAQ

Q. Are there any settings required for Google Sheets other than File > Share > "Share with others"?

A. No other settings are required besides adding prod-sheets-reader@money-forward-itmc.iam.gserviceaccount.com to "Share with others."

　 There are no requirements regarding the type of access permissions (Editor/Viewer) or the file location (Shared Drive/My Drive).

　　　

Q. Are there any templates for Google Sheets?

A. We recommend any format, so there are no specific templates; however, you can use the bulk registration template for accounts.

　　

Q. Can I update via API? 

A. API updates are not supported.

　　　

Q. Can I manage multiple sheets within a single Google Sheets file? Is this supported?

A. Yes, it is supported. Since the URL displayed in the browser differs for each sheet, please copy and paste the URL for the specific sheet you wish to link.

　　　　

Q. Can I register multiple licenses or roles in the custom app/spreadsheet integration?

A. Yes. Enter the values in the "licenses" and "roles" columns of your Google Sheet, separated by commas. After syncing with the Google Sheet, you can verify that multiple entries are registered in the custom app.

Example：To register three licenses "L1, L2, L3" under "licenses," enter "L1,L2,L3."

　　　　　To register two roles "R1, R2" under "roles," enter "R1,R2."　

　　　

Q. Is there a way to import only rows that meet specific conditions in Google Sheets?

A. Yes, you can use the FILTER function in Google Sheets to extract only the data that meets your conditions without editing the original source data.

　 Please refer to the configuration examples below and set your formulas accordingly.

Extraction Patterns

Formula used

Meaning of operators

Remarks

Extracting specific items only (e.g., "Active" only) Extracting

=FILTER(Sheet1!A:G, Sheet1!G:G = "Active")

= ( Equal to )

Displays rows where column G matches the specified text. 

items excluding specific ones (e.g., excluding "Retired") 

=FILTER(Sheet1!A:G, Sheet1!G:G <> "Retired")

<> (Not equal to)

Displays rows where column G does not match the specified text.

Extracting from a separate file while maintaining headers (e.g., excluding "Retired")

={ IMPORTRANGE("Source data URL", "Sheet1!A1:G1"); FILTER( IMPORTRANGE("Source data URL", "Sheet1!A2:G"), INDEX(IMPORTRANGE("Source data URL", "Sheet1!A2:G"), 0, 7) <> "Retired" ) }

<> (Not equal to)

Displays rows where column G is not "Retired". Pins the header in row 1.

Example：If you want to automatically display only the rows marked as "Active" in "Sheet 2" from the source data pasted into "Sheet 1"

　　Set the headers in the first row of "Sheet 2," and input the FILTER function into cell A2 of "Sheet 2" to extract the "Active" rows. When integrating, set the "Google Sheets file URL" to the URL of "Sheet 2."

　　　　

Example：If you want to automatically display data in "Sheet 2" from source data pasted into "Sheet 1," excluding rows marked as "Retired"

　　Set the FILTER function in cell A1 of "Sheet 2" to extract all rows except for "Retired." (Headers are required.)

　　When integrating, set the "Google Sheets file URL" to the URL of "Sheet 2."

　　　　　

Example：If you want to automatically display data in "Sheet 1" of an integration spreadsheet from source data in a management spreadsheet, excluding rows marked as "Retired"

　Set the function in cell A1 of "Sheet 1" in the integration spreadsheet to extract all rows except for "Retired." (Headers are required.)

　When integrating, set the "Google Sheets file URL" to the URL of "Sheet 1" of the integration spreadsheet.

How did we do?

Check SaaS license status

Contact