Google App Script for setting up a live sync between your Google Sheets and Vistaly 🚀

code.gs

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
      Logger.log(Session.getActiveUser());
      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);
  }
}