Skip to main content
Question

Data is available in reports but not via API

  • December 20, 2024
  • 0 replies
  • 11 views

Forum|alt.badge.img

1. Data is available in reports but not via API

Despite being able to output data for events after December 13, 2024, using the BOX report feature, I am unable to retrieve the same data via the BOX API. The API consistently returns only events up to December 13, 2024. Could you help clarify why this is happening and how to resolve it?

2. Scheduled executions do not save data after December 13

When I set up scheduled executions of my script to fetch event data, no events after December 13, 2024, are saved in my system. Could you explain why the API is not retrieving or saving these events during automated runs, even though the data exists in the reports?

 

// Function to fetch events from BOX API using the current stream_position
function fetchBoxEvents() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const scriptProperties = PropertiesService.getScriptProperties();
  const accessToken = scriptProperties.getProperty("BOX_ACCESS_TOKEN");

  if (!accessToken) {
    Logger.log("Access token is not set!");
    return;
  }

  const apiUrl = "https://api.box.com/2.0/events";
  let streamPosition = scriptProperties.getProperty("LAST_STREAM_POSITION") || "now"; // Start from the latest position if undefined
  let hasMoreData = true;

  // Add header row if the sheet is empty
  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
      "Event Type", "Created By", "User Login", "Created At (Tokyo Time)", "File Name", "Folder Path"
    ]);
  }

  while (hasMoreData) {
    const options = {
      method: "get",
      headers: {
        Authorization: `Bearer ${accessToken}`
      },
      muteHttpExceptions: true
    };

    try {
      // API request
      const response = UrlFetchApp.fetch(
        `${apiUrl}?stream_position=${streamPosition}`,
        options
      );
      const data = JSON.parse(response.getContentText());

      const events = data.entries;
      if (!events || events.length === 0) {
        Logger.log("No more events available.");
        hasMoreData = false;
        break;
      }

      events.forEach(event => {
        // Filter for ITEM_UPLOAD event type
        if (event.event_type !== "ITEM_UPLOAD") {
          Logger.log(`Ignored event type: ${event.event_type}`);
          return;
        }

        const source = event.source || {};
        const pathEntries = source.path_collection ? source.path_collection.entries : [];
        const folderPath = pathEntries.map(entry => entry.name).join("/");

        // Duplicate check (based on LAST_EVENT_ID)
        const lastEventId = scriptProperties.getProperty("LAST_EVENT_ID");
        if (lastEventId && event.event_id === lastEventId) {
          Logger.log(`Duplicate event skipped: ${event.event_id}`);
          return;
        }

        // Convert UTC to Tokyo Time
        const tokyoCreatedAt = convertToTokyoTime(event.created_at);

        // Write data to the spreadsheet
        const rowData = [
          event.event_type,                               // Event Type
          event.created_by ? event.created_by.name : "",  // Created By
          event.created_by ? event.created_by.login : "", // User Login
          tokyoCreatedAt,                                 // Created At (Tokyo Time)
          source.name || "",                              // File Name
          folderPath                                      // Folder Path
        ];
        sheet.appendRow(rowData);

        // Update LAST_EVENT_ID
        scriptProperties.setProperty("LAST_EVENT_ID", event.event_id);
      });

      // Update stream_position for the next request
      if (data.next_stream_position) {
        streamPosition = data.next_stream_position;
        scriptProperties.setProperty("LAST_STREAM_POSITION", streamPosition);
        Logger.log(`Saved next stream_position: ${streamPosition}`);
      } else {
        hasMoreData = false; // End loop if no further data
      }
    } catch (error) {
      Logger.log(`Error during API request: ${error.message}`);
      hasMoreData = false; // Stop loop on error
    }
  }
}

// Function to convert UTC date to Tokyo Time
function convertToTokyoTime(utcDateStr) {
  const utcDate = new Date(utcDateStr);
  const timeZone = "Asia/Tokyo";
  return Utilities.formatDate(utcDate, timeZone, "yyyy-MM-dd HH:mm:ss");
}

// Function to reset the stream_position to the oldest available position
function resetStreamPositionToOldest() {
  const scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty("LAST_STREAM_POSITION", "0"); // Reset to the oldest position
  Logger.log("Stream position has been reset to the oldest.");
}

 

0 replies

Be the first to reply!

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings