Google Sheets Sync

Documentation on how to sync a Google Sheet into a Table within a Collection in Scout.

This integration is in experimental beta and awaiting Google App Store approval. Until we get Google App Store approval, there are some extra steps to configure this integration. If you’re interested in sharing feedback, please reach out on Slack and let us know.

Scout integrates directly with Google Sheets. Learn how to seamlessly sync each row of a Google Sheet into a Table within a Collection in Scout.

Setting up the integration

1

Set header row

First, ensure the first row of your Google Sheet is a header row labeling each column. Scout will use the header row values to label the value in each column in the collection.

2

Creating the extension

In the header menu of your Google Sheet, click Extensions, then click App Script.

3

Configuring the extension

Rename the project from Untitled Project to Scout Sheets Sync and replace all of the code in Code.gs with the following:

Code.gs
1// Constants
2const SCOUT_API_BASE_URL = 'https://api-prod.scoutos.com/v2';
3const API_KEY_PROPERTY = 'secret_XXXXX';
4
5// Function to get the API key from User Properties
6function getApiKey() {
7  const userProperties = PropertiesService.getUserProperties();
8  let apiKey = userProperties.getProperty(API_KEY_PROPERTY);
9  
10  if (!apiKey) {
11    // Prompt the user to enter their API key
12    const ui = SpreadsheetApp.getUi();
13    const response = ui.prompt('Enter your Scout Secret Key');
14    if (response.getSelectedButton() == ui.Button.OK) {
15      apiKey = response.getResponseText();
16      if (apiKey) {
17        userProperties.setProperty(API_KEY_PROPERTY, apiKey);
18      } else {
19        ui.alert('Scout Secret Key is required for this add-on to function.');
20      }
21    } else {
22      ui.alert('Scout Secret Key is required for this add-on to function.');
23    }
24  }
25  
26  return apiKey;
27}
28
29// Function to create the custom menu
30function onOpen(e) {
31  const ui = SpreadsheetApp.getUi();
32  ui.createMenu('*️⃣ Scout') // Static menu name
33    .addItem('Sync Google Sheet to Scout Table', 'showSyncInterface') // Static item name
34    .addToUi();
35}
36
37// Function to get sync status and last synced collection and table
38function getSyncStatus() {
39  const properties = PropertiesService.getDocumentProperties();
40  const syncStatus = properties.getProperty('syncStatus') || 'Not synced yet';
41  const lastSyncTime = properties.getProperty('lastSyncTime') || 'N/A';
42  const lastCollectionName = properties.getProperty('lastCollectionName') || 'N/A';
43  const lastTableName = properties.getProperty('lastTableName') || 'N/A';
44
45  return {
46    syncStatus: syncStatus,
47    lastSyncTime: lastSyncTime,
48    lastCollectionName: lastCollectionName,
49    lastTableName: lastTableName
50  };
51}
52
53// Function to show the sync interface
54function showSyncInterface() {
55  const html = HtmlService.createHtmlOutputFromFile('syncToScout')
56    .setWidth(600)
57    .setHeight(500);
58  SpreadsheetApp.getUi().showModalDialog(html, 'Sync Google Sheet to Scout Table');
59}
60
61// Function to get the base URL of the spreadsheet
62function getSpreadsheetBaseUrl() {
63  const url = SpreadsheetApp.getActiveSpreadsheet().getUrl();
64  // Remove '/edit' and anything after
65  const index = url.indexOf('/edit');
66  if (index !== -1) {
67    return url.substring(0, index);
68  } else {
69    // In case '/edit' is not present
70    const questionMarkIndex = url.indexOf('?');
71    if (questionMarkIndex !== -1) {
72      return url.substring(0, questionMarkIndex);
73    } else {
74      return url;
75    }
76  }
77}
78
79// Function to start syncing (called from client-side)
80function startSync() {
81  const properties = PropertiesService.getDocumentProperties();
82  const apiKey = getApiKey();
83  if (!apiKey) throw new Error('API Key not found.');
84
85  properties.setProperty('syncStatus', 'Syncing to Scout 🟡');
86  properties.setProperty('lastSyncTime', new Date().toISOString());
87
88  // Refresh the menu to reflect the new status
89  onOpen();
90
91  // Run the sync process
92  try {
93    const syncResult = syncDataToScout(apiKey);
94    properties.setProperty('syncStatus', 'Synced to Scout 🟢');
95    properties.setProperty('lastSyncTime', new Date().toISOString());
96
97    // Refresh the menu to reflect the new status
98    onOpen();
99
100    return { success: true, collectionId: syncResult.collectionId, tableId: syncResult.tableId, message: 'Data synced successfully.' };
101  } catch (error) {
102    properties.setProperty('syncStatus', 'Scout sync failed 🔴');
103    properties.setProperty('lastSyncTime', new Date().toISOString());
104
105    // Refresh the menu to reflect the new status
106    onOpen();
107
108    return { success: false, message: 'Error syncing data: ' + error.message };
109  }
110}
111
112// Function to sync data to Scout
113function syncDataToScout(apiKey) {
114  const properties = PropertiesService.getDocumentProperties();
115  const sheet = SpreadsheetApp.getActiveSheet();
116  const dataRange = sheet.getDataRange();
117  const data = dataRange.getValues();
118
119  if (data.length < 2) {
120    throw new Error('No data to sync.');
121  }
122
123  // Get headers from the first row
124  let headers = data[0];
125
126  // Remove any leading/trailing whitespace from headers
127  headers = headers.map(header => header.toString().trim());
128
129  // Build documents array
130  const documents = [];
131  const timestamp = Math.floor(Date.now() / 1000); // UNIX timestamp in seconds
132  const spreadsheetUrl = getSpreadsheetBaseUrl();
133
134  for (let i = 1; i < data.length; i++) {
135    let row = data[i];
136
137    const rowObject = {};
138    for (let j = 0; j < headers.length; j++) {
139      rowObject[headers[j]] = row[j];
140    }
141
142    const document = {
143      url: spreadsheetUrl,
144      last_refreshed_at: timestamp,
145      content: JSON.stringify(rowObject)
146    };
147
148    documents.push(document);
149  }
150
151  // Step 1: Check or create "Google Sheets Sync" collection
152  const collectionInfo = getOrCreateCollection(apiKey);
153  const collectionId = collectionInfo.collectionId;
154  const collectionName = collectionInfo.collectionName;
155
156  // Step 2: Check or create table with description matching spreadsheet URL
157  const tableInfo = getOrCreateTable(collectionId, spreadsheetUrl, apiKey);
158  const tableId = tableInfo.tableId;
159  const tableName = tableInfo.tableName;
160
161  // Save collection and table info
162  properties.setProperty('lastCollectionId', collectionId);
163  properties.setProperty('lastCollectionName', collectionName);
164  properties.setProperty('lastTableId', tableId);
165  properties.setProperty('lastTableName', tableName);
166
167  // Step 3: Sync documents to table - this will delete all old documents
168  // and write the current sheet content as documents in the table
169  syncDocumentsToTable(collectionId, tableId, documents, apiKey);
170
171  return { success: true, collectionId: collectionId, tableId: tableId };
172}
173
174// Function to get or create "Google Sheets Sync" collection
175// Function to get or create "Google Sheets Sync" collection
176function getOrCreateCollection(apiKey) {
177  const collections = fetchCollections(apiKey);
178  let collection = collections.find(
179    (c) =>
180      c.collection_config.collection_display_name === 'Google Sheets Sync'
181  );
182
183  if (collection) {
184    return {
185      collectionId: collection.collection_id,
186      collectionName: collection.collection_config.collection_display_name,
187    };
188  } else {
189    // Create collection
190    const collectionImgUrl =
191      'https://ghexww3n55fveb5i.public.blob.vercel-storage.com/5968557-aoNUKMTwuL9LQ2NDnbr6o2kiwKIqax.png';
192    const collectionDescription = 'This collection is synced with Google Sheets.';
193
194    const payload = {
195      collection_display_name: 'Google Sheets Sync',
196      collection_img_url: collectionImgUrl,
197      collection_description: collectionDescription,
198    };
199
200    const url = SCOUT_API_BASE_URL + '/collections';
201    const options = {
202      method: 'post',
203      headers: {
204        Authorization: 'Bearer ' + apiKey,
205        'Content-Type': 'application/json',
206      },
207      payload: JSON.stringify(payload),
208      muteHttpExceptions: true,
209    };
210
211    try {
212      const response = UrlFetchApp.fetch(url, options);
213      const responseCode = response.getResponseCode();
214      const responseBody = response.getContentText();
215
216      if (responseCode === 200 || responseCode === 201) {
217        const jsonResponse = JSON.parse(responseBody);
218        const newCollection = jsonResponse.data;
219        const collectionId = newCollection.collection_id;
220
221        // Fetch tables in the new collection
222        const tablesUrl = SCOUT_API_BASE_URL + '/collections/' + collectionId + '/tables';
223        const tablesOptions = {
224          method: 'get',
225          headers: {
226            Authorization: 'Bearer ' + apiKey,
227            'Content-Type': 'application/json',
228          },
229          muteHttpExceptions: true,
230        };
231
232        const tablesResponse = UrlFetchApp.fetch(tablesUrl, tablesOptions);
233        const tablesResponseCode = tablesResponse.getResponseCode();
234        const tablesResponseBody = tablesResponse.getContentText();
235
236        if (tablesResponseCode === 200) {
237          const tablesData = JSON.parse(tablesResponseBody).data;
238
239          // Find the default 'Untitled' table
240          const defaultTable = tablesData.find(
241            (table) => table.table_config.table_display_name === 'Untitled'
242          );
243
244          if (defaultTable) {
245            const tableId = defaultTable.table_id;
246
247            // Delete the default table
248            const deleteTableUrl =
249              SCOUT_API_BASE_URL + '/collections/' + collectionId + '/tables/' + tableId;
250            const deleteOptions = {
251              method: 'delete',
252              headers: {
253                Authorization: 'Bearer ' + apiKey,
254                'Content-Type': 'application/json',
255              },
256              muteHttpExceptions: true,
257            };
258
259            const deleteResponse = UrlFetchApp.fetch(deleteTableUrl, deleteOptions);
260            const deleteResponseCode = deleteResponse.getResponseCode();
261
262            if (deleteResponseCode === 200 || deleteResponseCode === 204) {
263              // Table deleted successfully
264            } else {
265              throw new Error('Error deleting default table: ' + deleteResponse.getContentText());
266            }
267          }
268        } else {
269          throw new Error('Error fetching tables: ' + tablesResponseBody);
270        }
271
272        return {
273          collectionId: collectionId,
274          collectionName: newCollection.collection_config.collection_display_name,
275        };
276      } else {
277        throw new Error('Error creating collection: ' + responseBody);
278      }
279    } catch (error) {
280      throw new Error('Error creating collection: ' + error.message);
281    }
282  }
283}
284
285// Function to get or create table with description matching spreadsheet URL
286function getOrCreateTable(collectionId, spreadsheetUrl, apiKey) {
287  const tables = fetchTables(collectionId, apiKey);
288
289  //let table = tables.find(t => t.table_config.table_description === spreadsheetUrl);
290  let table = tables.find(t => t.table_config.table_display_name.includes(SpreadsheetApp.getActiveSpreadsheet().getId()));
291
292
293  if (table) {
294    // Table exists
295    return {
296      tableId: table.table_id,
297      tableName: table.table_config.table_display_name,
298      isNewTable: false
299    };
300  } else {
301    // Create table
302    const tableName = SpreadsheetApp.getActiveSpreadsheet().getName()+" - "+SpreadsheetApp.getActiveSpreadsheet().getId();
303    const tableImgUrl = 'https://ghexww3n55fveb5i.public.blob.vercel-storage.com/5968557-aoNUKMTwuL9LQ2NDnbr6o2kiwKIqax.png';
304    const tableDescription = spreadsheetUrl;
305
306    // Define fixed schema
307    const schema = [
308      {
309        column_id: 'url',
310        column_display_name: 'url',
311        column_type: 'url',
312        data_type: 'string',
313        hidden: false,
314        default: null
315      },
316      {
317        column_id: 'last_refreshed_at',
318        column_display_name: 'last_refreshed_at',
319        column_type: 'number',
320        data_type: 'number',
321        hidden: false,
322        default: null
323      },
324      {
325        column_id: 'content',
326        column_display_name: 'content',
327        column_type: 'text-long',
328        data_type: 'string',
329        hidden: false,
330        default: null
331      }
332    ];
333
334    const payload = {
335      table_display_name: tableName,
336      table_img_url: tableImgUrl,
337      table_description: tableDescription,
338      schema: schema
339    };
340
341    const url = `${SCOUT_API_BASE_URL}/collections/${collectionId}/tables`;
342    const options = {
343      method: 'post',
344      headers: {
345        'Authorization': 'Bearer ' + apiKey,
346        'Content-Type': 'application/json'
347      },
348      payload: JSON.stringify(payload),
349      muteHttpExceptions: true
350    };
351
352    try {
353      const response = UrlFetchApp.fetch(url, options);
354      const responseCode = response.getResponseCode();
355      const responseBody = response.getContentText();
356
357      if (responseCode === 200 || responseCode === 201) {
358        const jsonResponse = JSON.parse(responseBody);
359        const newTable = jsonResponse.data;
360        return {
361          tableId: newTable.table_id,
362          tableName: newTable.table_config.table_display_name,
363          isNewTable: true
364        };
365      } else {
366        throw new Error('Error creating table: ' + responseBody);
367      }
368
369    } catch (error) {
370      throw new Error('Error creating table: ' + error.message);
371    }
372  }
373}
374
375// Function to delete all documents in a table
376function deleteAllDocumentsInTable(collectionId, tableId, apiKey) {
377  // Get all documents in the table
378  const url = `${SCOUT_API_BASE_URL}/collections/${collectionId}/tables/${tableId}/documents`;
379  const options = {
380    method: 'get',
381    headers: {
382      'Authorization': 'Bearer ' + apiKey,
383      'Content-Type': 'application/json'
384    },
385    muteHttpExceptions: true
386  };
387
388  try {
389    const response = UrlFetchApp.fetch(url, options);
390    const responseCode = response.getResponseCode();
391    const responseBody = response.getContentText();
392
393    if (responseCode === 200) {
394      const jsonResponse = JSON.parse(responseBody);
395      const documents = jsonResponse.data;
396
397      for (let i = 0; i < documents.length; i++) {
398        const documentId = documents[i].document_id;
399        deleteDocument(collectionId, tableId, documentId, apiKey);
400      }
401
402    } else {
403      throw new Error('Error fetching documents: ' + responseBody);
404    }
405
406  } catch (error) {
407    throw new Error('Error deleting documents: ' + error.message);
408  }
409}
410
411// Function to upload documents to a table
412function syncDocumentsToTable(collectionId, tableId, documents, apiKey) {
413  //throw new Error(JSON.stringify(documents));
414  const url = `${SCOUT_API_BASE_URL}/collections/${collectionId}/tables/${tableId}/sync`;
415  const options = {
416    method: 'post',
417    headers: {
418      'Authorization': `Bearer ${apiKey}`,
419      'Content-Type': 'application/json'
420    },
421    payload: JSON.stringify(documents), // Send all documents in a single request
422    muteHttpExceptions: true
423  };
424
425  try {
426    const response = UrlFetchApp.fetch(url, options);
427    const responseCode = response.getResponseCode();
428    const responseBody = response.getContentText();
429
430    if (responseCode === 200 || responseCode === 201) {
431      Logger.log(`All ${documents.length} documents uploaded successfully.`);
432    } else {
433      Logger.log(`Error uploading documents: ` + responseBody);
434      throw new Error(`Error uploading documents: ` + responseBody);
435    }
436  } catch (error) {
437    Logger.log('Error uploading documents: ' + error.message);
438    throw error;
439  }
440}
441
442// Function to fetch collections from Scout API
443function fetchCollections(apiKey) {
444  const url = SCOUT_API_BASE_URL + '/collections';
445  const options = {
446    method: 'get',
447    headers: {
448      'Authorization': 'Bearer ' + apiKey,
449      'Content-Type': 'application/json'
450    },
451    muteHttpExceptions: true
452  };
453  
454  try {
455    const response = UrlFetchApp.fetch(url, options);
456    const responseCode = response.getResponseCode();
457    const responseBody = response.getContentText();
458    
459    if (responseCode === 200) {
460      const jsonResponse = JSON.parse(responseBody);
461      return jsonResponse.data;
462    } else {
463      Logger.log('Error fetching collections: ' + responseBody);
464      return [];
465    }
466  } catch (error) {
467    Logger.log('Exception fetching collections: ' + error);
468    return [];
469  }
470}
471
472// Function to fetch tables from Scout API
473function fetchTables(collectionId, apiKey) {
474  const url = SCOUT_API_BASE_URL + '/collections/' + collectionId + '/tables';
475  const options = {
476    method: 'get',
477    headers: {
478      'Authorization': 'Bearer ' + apiKey,
479      'Content-Type': 'application/json'
480    },
481    muteHttpExceptions: true
482  };
483  
484  try {
485    const response = UrlFetchApp.fetch(url, options);
486    const responseCode = response.getResponseCode();
487    const responseBody = response.getContentText();
488    
489    if (responseCode === 200) {
490      const jsonResponse = JSON.parse(responseBody);
491      return jsonResponse.data;
492    } else {
493      Logger.log('Error fetching tables: ' + responseBody);
494      return [];
495    }
496  } catch (error) {
497    Logger.log('Exception fetching tables: ' + error);
498    return [];
499  }
500}
4

Adding your Scout secret key

Copy your Scout secret key from the Scout dashboard. Then in the new Code.gs script, you’ll see API_KEY_PROPERTY defined with a placeholder. Paste your key to replace the secret_XXXXX placeholder value.

Then, click the Save project to Drive button in the menu. This saves your newly created Code.gs file.

Anyone with editor access can look at the script, and see your Scout secret key in the script! Do not share editor access with anyone that shouldn’t be able to see that key. You can enter the key manually on each sync by simply deleting the const API_KEY_PROPERTY = 'secret_YOUR_PASTED_KEY'; definition. You will then be prompted to enter the key every time you want to run a sync.

5

Add the integration interface

Next, you’ll need to add the integration interface. Click the + icon in the menu. This will create a new file. Name it syncToScout.

Paste the following code snippet into the syncToScout.html file and click save.

syncToScout.html
1<!DOCTYPE html>
2<html>
3<head>
4  <base target="_top">
5  <!-- Include Google's CSS for add-ons -->
6  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
7  <style>
8    body {
9      font-family: 'Roboto', sans-serif;
10      margin: 0;
11      padding: 0;
12    }
13    #container {
14      padding: 20px;
15      max-width: 500px;
16      margin: auto;
17    }
18    #header {
19      text-align: center;
20      margin-bottom: 20px;
21    }
22    #logo {
23      width: 150px;
24      margin-bottom: 10px;
25    }
26    #banner {
27      /* background-color: #f9f9f9; */
28      padding: 5px;
29      margin-bottom: 5px;
30      text-align: center;
31      font-size: 15px;
32    }
33    #banner a {
34      color: #4285f4;
35      text-decoration: none;
36      font-weight: bold;
37    }
38    #status {
39      margin-bottom: 20px;
40      font-size: 14px;
41    }
42    #message {
43      margin-bottom: 20px;
44      font-size: 14px;
45      text-align: center;
46    }
47    #message a {
48      color: #4285f4;
49      text-decoration: none;
50      font-weight: bold;
51    }
52    #buttons {
53      text-align: center;
54      margin-bottom: 20px;
55    }
56    #buttons button {
57      margin: 5px;
58      min-width: 150px;
59    }
60    #footer {
61      text-align: center;
62      /* font-size: 12px;
63      color: gray; */
64    }
65    /* #footer a {
66      color: gray;
67      text-decoration: none;
68      margin: 0 5px;
69    }
70    #footer a:hover {
71      text-decoration: underline;
72    } */
73    .hidden {
74      display: none;
75    }
76    .muted {
77      color: #999;
78      font-size: 12px;
79    }
80    .action-button {
81      /* background-color: #4285f4;
82      color: white;
83      border: none;
84      padding: 10px 20px;
85      font-size: 14px; */
86      cursor: pointer;
87    }
88    .action-button:disabled {
89      /* background-color: #cccccc; */
90      cursor: not-allowed;
91    }
92    .secondary-button {
93      /* background-color: #f1f1f1;
94      color: #333;
95      border: none;
96      padding: 10px 20px;
97      font-size: 14px; */
98      cursor: pointer;
99    }
100    /* .secondary-button:hover {
101      background-color: #e1e1e1;
102    } */
103    table {
104      width: 100%;
105      border-collapse: collapse;
106      margin-bottom: 20px;
107    }
108    th, td {
109      text-align: center;
110      padding: 10px;
111    }
112    th {
113      background-color: #f1f1f1;
114      font-weight: bold;
115    }
116    tbody tr:nth-child(even) {
117      background-color: #fafafa;
118    }
119  </style>
120</head>
121<body>
122  <div id="container">
123    <div id="header">
124      <img id="logo" src="https://fdr-prod-docs-files-public.s3.amazonaws.com/https://scoutos.docs.buildwithfern.com/2024-11-22T00:52:02.732Z/assets/logo-light.svg" alt="Scout Logo">
125    </div>
126
127    <div id="message" class="hidden">
128      <span style="color:3d9400;"><b>Sync successful!</b></span>
129      <br />
130      <br /> 
131      Review <a href="https://studio.scoutos.com/collections/v2" target="_blank" id="collectionLink">your updated collection in Scout</a> and close this modal.
132    </div>
133
134    <div id="status">
135      <table>
136        <thead>
137          <tr>
138            <th>Last Sync Status</th>
139            <th>Last Sync Time</th>
140          </tr>
141        </thead>
142        <tbody>
143          <tr>
144            <td>
145              <span id="syncStatus">Not synced yet</span>
146            </td>
147            <td>
148              <span id="lastSyncTimeFormatted"><b>Never</b></span><br />
149              <span class="muted" id="lastSyncTimeDetailed">N/A</span>
150            </td>
151          </tr>
152        </tbody>
153      </table>
154    </div>
155
156    <div id="buttons">
157      <button id="closeButton" class="secondary-button">Close</button>
158      <button id="syncButton" class="action action-button">Sync Sheet to Scout</button>
159    </div>
160
161    <div id="banner">
162      <span class="secondary"><b>Important!</b> The first row of the sheet is treated a header row with keys/column names.</span>
163    </div>
164
165    <div id="footer" class="secondary">
166      <a href="https://studio.scoutos.com/sign-in">Scout Integration Guide</a> | <a href="https://studio.scoutos.com/sign-in">Access Scout</a> |
167      <a href="https://studio.scoutos.com/slack">Scout Slack Community</a>
168    </div>
169  </div>
170
171  <script>
172    function updateStatus(statusInfo) {
173      document.getElementById('syncStatus').innerText = statusInfo.syncStatus;
174
175      if (statusInfo.lastSyncTime && statusInfo.lastSyncTime !== 'N/A') {
176        const timeInfo = formatSyncTime(statusInfo.lastSyncTime);
177        document.getElementById('lastSyncTimeFormatted').innerHTML = '<b>' + timeInfo.display + '</b>';
178        document.getElementById('lastSyncTimeDetailed').innerText = timeInfo.detailed;
179      } else {
180        document.getElementById('lastSyncTimeFormatted').innerHTML = '<b>Never</b>';
181        document.getElementById('lastSyncTimeDetailed').innerText = 'N/A';
182      }
183    }
184
185    function formatSyncTime(lastSyncTimestamp) {
186      const now = new Date();
187      const lastSyncTime = new Date(lastSyncTimestamp);
188      const diffInMs = now - lastSyncTime; // Difference in milliseconds
189      const diffInMinutes = Math.floor(diffInMs / 60000); // Difference in minutes
190
191      let timeDisplay = "";
192      if (diffInMinutes < 60) {
193        timeDisplay = `${diffInMinutes} minute${diffInMinutes === 1 ? "" : "s"} ago`;
194      } else if (diffInMinutes < 1440) {
195        const diffInHours = Math.floor(diffInMinutes / 60);
196        timeDisplay = `${diffInHours} hour${diffInHours === 1 ? "" : "s"} ago`;
197      } else {
198        const diffInDays = Math.floor(diffInMinutes / 1440);
199        timeDisplay = `${diffInDays} day${diffInDays === 1 ? "" : "s"} ago`;
200      }
201
202      // Format detailed time as "DD/MM/YY hh:mm am/pm"
203      const optionsDate = {
204        day: '2-digit',
205        month: '2-digit',
206        year: '2-digit',
207      };
208      const optionsTime = {
209        hour: 'numeric',
210        minute: '2-digit',
211        hour12: true
212      };
213      const formattedDate = lastSyncTime.toLocaleDateString('en-US', optionsDate);
214      const formattedTime = lastSyncTime.toLocaleTimeString('en-US', optionsTime);
215
216      return {
217        display: timeDisplay,
218        detailed: `${formattedDate} ${formattedTime}`
219      };
220    }
221
222    function setSyncButtonDisabled(disabled, text) {
223      const syncButton = document.getElementById('syncButton');
224      syncButton.disabled = disabled;
225      syncButton.innerText = text;
226    }
227
228    document.getElementById('syncButton').addEventListener('click', function() {
229      setSyncButtonDisabled(true, 'Sync in progress...');
230      google.script.run.withSuccessHandler(function(response) {
231        if (response.success) {
232          document.getElementById('message').classList.remove('hidden');
233          // Update the collection link
234          document.getElementById('collectionLink').href = 'https://studio.scoutos.com/collections/'+response.collectionId+'?table='+response.tableId; // Replace with actual link
235          // Disable sync button
236          setSyncButtonDisabled(true, 'Sync Complete');
237          // Update status
238          google.script.run.withSuccessHandler(updateStatus).getSyncStatus();
239        } else {
240          alert(response.message);
241          // Re-enable sync button
242          setSyncButtonDisabled(false, 'Sync Sheet to Scout');
243        }
244      }).withFailureHandler(function(error) {
245        alert('Error syncing data: ' + error.message);
246        // Re-enable sync button
247        setSyncButtonDisabled(false, 'Sync Sheet to Scout');
248      }).startSync();
249    });
250
251    document.getElementById('closeButton').addEventListener('click', function() {
252      google.script.host.close();
253    });
254
255    // Initialize status
256    google.script.run.withSuccessHandler(updateStatus).getSyncStatus();
257  </script>
258</body>
259</html>
6

Granting permissions

Now you can return to your Google Sheet and refresh the page. You should see a Scout icon in the menu above the sheet now. Click that icon.

You will be prompted to grant permissions to the script. Click Ok.

You will be prompted with warnings indicating that Google hasn’t verified this app - in this case, you are the developer of the app because it’s your script. Click Advanced at the bottom of the modal, then click Go to Scout Sheets Sync (unsafe) to grant permission to yourself to run your script.

Then you will select your Google account to proceed.

7

Running the integration

Now in your Google Sheet, click the Scout menu item, and click Sync Google Sheet to Scout Table.

Then, in the modal that opens, click Sync Sheet to Scout to run the sync.

Once completed, you’ll see a status and a link to click through into Scout to review the documents that have been uploaded.

Built with