How to Auto Backup or Import Data From API JSON Data into Google Sheets Using Google AppScripts ?

Here Today Explain about Auto Backup or Import Data From API JSON Data into Google Sheets Using Google AppScripts.

To update data in a Google Sheet using JSON data from an API, you can use Google Apps Script, which allows you to automate tasks within Google Workspace applications, including Google Sheets. Here are the general steps to achieve this:

Create a Google Sheet:

If you haven’t already, create a Google Sheet where you want to update the data from the API.

Enable Google Apps Script

Inside your Google Sheet, click on “Extensions” -> “Apps Script” to open the Google Apps Script editor.

recommended Read this Article : How to Deploy Code in Google AppScripts

Write the Google Apps Script Code:

In the script editor, you can write a script to fetch JSON data from an API and update your Google Sheet. Here’s a simple example:

function updateSheetWithAPI() {
  // Define the API URL
  var apiUrl = "YOUR_API_URL"; // Replace with your API URL

  try {
    // Fetch JSON data from the API
    var response = UrlFetchApp.fetch(apiUrl);

    // Check the response status code
    if (response.getResponseCode() === 200) {
      var json = response.getContentText();
      var data = JSON.parse(json);

      // Access your Google Sheet
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Replace "Sheet1" with your sheet name

      // Clear existing data in the sheet (optional)
      sheet.clear();

      // Write the JSON data to the sheet
      if (data.result && data.result.files && data.result.files.length > 0) {
        var headers = Object.keys(data.result.files[0]);
        sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
        for (var i = 0; i < data.result.files.length; i++) {
          var rowData = [];
          for (var j = 0; j < headers.length; j++) {
            rowData.push(data.result.files[i][headers[j]]);
          }
          sheet.getRange(i + 2, 1, 1, rowData.length).setValues([rowData]);
        }
      } else {
        // Handle the case where there is no data in the JSON response
        Logger.log("No data found in the JSON response.");
      }
    } else {
      // Handle non-200 HTTP response codes
      Logger.log("HTTP response code: " + response.getResponseCode());
    }
  } catch (error) {
    // Handle any errors that occur during the execution of the script
    Logger.log("An error occurred: " + error.toString());
  }
}

Save the Script:

After writing the script, click the floppy disk icon to save it.

Run the Script:

You can run the updateSheetWithAPI function by clicking the play button (▶️) or by manually executing it. It will fetch data from the API and update your Google Sheet accordingly.

Set Up Triggers :

If you want the script to run automatically at specific intervals, you can set up triggers using the “Triggers” menu in the Apps Script editor.

• In AppScripts Page Left Side Click Clock Symbol then Click Triggers.
• then Click Add Trigger Button.
• then Select type of time based trigger Select option minutes Timer
• then Select minute interval Select option Every Minute (your choice)
• then click Save Button

Every Minute Update Your google Sheet.

Please replace “YOUR_API_URL” with the actual URL of the API you want to fetch data from. Additionally, make sure you have the necessary permissions to access the API from Google Apps Script, as some APIs may require authentication.

Remember that this is a basic example, and you may need to modify the script to suit your specific requirements, especially if the JSON data structure or the way you want to update the sheet is more complex.