Google Sheets

Use the Google Sheets integration to get data teams are managing in spreadsheets into Vistaly.

Setting up a direct sync

Following these steps will create a live sync between your Google Sheets and Vistaly.

• Step 1: Format your Sheet
• Step 2: Add the Google App Script
• Step 3: Create/Enter your API Key
• Step 4: Set the “On edit” Trigger

Step 1 (Format your Sheet)

Add the following column headers to your spreadsheet. You can rename these, but the order is important if you don’t want to modify the App Script code shared in the next step.

Note: Both Column D and Column E automatically update once a change to the metric value has been saved in Vistaly.

Column A (Vistaly Card ID)

This is the identifier that will link this row of your spreadsheet to a Vistaly card. You can only link Vistaly Cards that support numeric values (KPIs, Outcomes, Key Results, etc…)

To find a Card ID, open your Vistaly workspace, select the card you want to link and copy the last part of the URL (the tsnid query parameter)

ex: vistaly.com/w/IGOeM6Ymm1q8bcnYsuZcb?tsnid=5tx6DnTlsCRCgnWGWXjZ1

Additional Columns

You can add any additional columns you need after these 5 required columns without issue.

Step 2 (Add the Google App Script)

The App Script will set up a live sync between your spreadsheet and Vistaly.

Select Extensions > App Script and paste the App Script Code into the Editor.

function onEditHandler(e) {
  try {
    // 1. Determine what was edited
    const range = e.range;
    const row = range.getRow();
    const col = range.getColumn();

    // 2. Check if the edited cell is in column C (col = 3)
    if (col === 3) {
      const sheet = range.getSheet();

      // 3. Update column D (Last Updated) with the current timestamp
      const timestamp = new Date().toISOString();
      sheet.getRange(row, 4).setValue(timestamp); // Column D = 4

      // 4. Update column E (Last Updated By) with the editor's email
      const userEmail = Session.getActiveUser().getEmail() || 'Unknown User';
      sheet.getRange(row, 5).setValue(userEmail); // Column E = 5

      // 5. Get the value in column A (ID) and column C (numeric value)
      const colAValue = sheet.getRange(row, 1).getValue(); // Column A = 1
      const numericValueC = Number(range.getValue()); // Column C value (numeric)

      // 6. Check that column A has a valid ID (non-empty string)
      if (typeof colAValue === 'string' && colAValue.trim() !== '') {
        // 7. Construct the API payload
        const payloadObj = {
          timestamp: timestamp,
          value: numericValueC
        };
        const payload = JSON.stringify(payloadObj);

        // 8. Build the API URL
        const url = `https://api.vistaly.com/v1/cards/${colAValue}/metrics`;

        // 9. Prepare fetch options, including the API Key
        const apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
        const options = {
          method: 'post',
          contentType: 'application/json',
          headers: {
            Authorization: `Bearer ${apiKey}`
          },
          payload: payload,
          muteHttpExceptions: true
        };

        // 10. Make the POST request
        const response = UrlFetchApp.fetch(url, options);

        // 11. (Optional) Log the response for debugging
        Logger.log('URL: ' + url);
        Logger.log('Payload: ' + payload);
        Logger.log('Response code: ' + response.getResponseCode());
        Logger.log('Response body: ' + response.getContentText());
      }
    }
  } catch (err) {
    Logger.log('Error in onEditHandler: ' + err);
  }
}

Step 3 (Create/Enter your API Key)

To authenticate your Google Sheet with your Vistaly workspace, you first need to add your API Key. If you haven’t created an API Key yet, follow these steps to create one.

Then add your API Key in the Google App Script Editor by selecting the Settings page and then scrolling down to Script Properties. Add the property as API_KEY and the value as your secret Vistaly API Key and select Save script properties.

Step 4 (Set the “On edit” Trigger)

The last thing you need to do is tell Google how you want the App Script to run.

Go to the Triggers, page and update the for to reference your App Script.

Note: If you haven’t set something like this up before, you will have to authorize Google to talk to external sources.

Testing it out

You’re all set. Whenever you make changes to a value in your spreadsheet, as long as the Vistaly Card ID is set in the corresponding row, it will automatically update Vistaly.

As always, if you experience any issues getting set up, reach out to support@vistaly.com.