make appscript to check ip in cell return color green > online, and keep check every 5 min
- User
make appscript to check ip in cell return color green > online, and keep check every 5 min
- Assistant
Certainly! Here's a step-by-step guide to create a Google Apps Script that checks the IP addresses listed in specific cells of your Google Sheet. The script will change the background color of each cell to green if the IP is online, and it will automatically run every 5 minutes.
Step 1: Prepare Your Google Sheet
- Enter Your IP Addresses: In your Google Sheet, list the IP addresses you want to monitor in a single column (e.g., Column A). For example, you might have IP addresses in cells A1 through A10.
Step 2: Open the Script Editor
- Access the Script Editor:
- Click on
Extensionsin the top menu. - Select
Apps Script. This will open a new tab with the Apps Script editor.
- Click on
Step 3: Write the Apps Script
-
Copy and Paste the Script: Delete any placeholder code in the editor and paste the following script:
function checkIPs() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange("A1:A10"); // Adjust the range to match where your IPs are listed var values = range.getValues(); for (var i = 0; i < values.length; i++) { var ip = values[i][0]; if (ip) { try { // Attempt to fetch data from the IP address var response = UrlFetchApp.fetch("http://" + ip, { muteHttpExceptions: true, timeout: 5 }); var code = response.getResponseCode(); if (code >= 200 && code < 400) { // If the response code indicates success, mark as online range.getCell(i + 1, 1).setBackground("green"); } else { // If the response code indicates an error, mark as offline range.getCell(i + 1, 1).setBackground("red"); } } catch (e) { // If an exception occurs (e.g., timeout), mark as offline range.getCell(i + 1, 1).setBackground("red"); } } } }Note: Adjust
"A1:A10"ingetRange("A1:A10")to match the range where your IP addresses are located.
Step 4: Save the Script
- Name the Project: Click on the
Untitled projecttitle at the top left and give your script project a name (e.g.,IP Status Checker). - Save: Click the disk icon or press
Ctrl + S(Windows) orCmd + S(Mac) to save the script.
Step 5: Run the Script Manually Once
-
Authorize the Script:
- Click the
Runbutton (▶️) to execute thecheckIPsfunction. - You will be prompted to authorize the script to access your spreadsheet and make external requests.
- Follow the prompts to allow the necessary permissions.
- Click the
-
Check the Results:
- Return to your Google Sheet.
- You should see the background colors of the cells with IP addresses change to green or red based on their online status.
Step 6: Set Up the Trigger to Run Every 5 Minutes
-
Open Trigger Settings:
- In the Apps Script editor, click on the
Triggersicon on the left sidebar. It looks like a clock. - Alternatively, click on
Triggersin the left-hand menu.
- In the Apps Script editor, click on the
-
Create a New Trigger:
- Click on the
+ Add triggerbutton in the bottom right corner.
- Click on the
-
Configure the Trigger:
- Choose which function to run: Select
checkIPs. - Select event source: Choose
Time-driven. - Select type of time based trigger: Choose
Minutes timer. - Select minute interval: Choose
Every 5 minutes. - Failure notification settings: Set as desired (e.g., notify immediately).
- Choose which function to run: Select
-
Save the Trigger:
- Click
Save. - You may be prompted again to authorize the script to run automatically. Follow the prompts to grant permission.
- Click
Step 7: Verify the Trigger Execution
-
Check Execution Logs:
- Back in the Apps Script editor, click on
Executionsin the left-hand menu to view the execution logs. - You can monitor if the function is running every 5 minutes and see any errors if they occur.
- Back in the Apps Script editor, click on
-
Observe the Google Sheet:
- Keep your Google Sheet open and observe the updates.
- The background colors of the IP address cells should update every 5 minutes based on their online status.
Additional Notes
-
Customizing the Range: If your IP addresses are not in Column A or are in a different range, adjust the
getRange("A1:A10")accordingly. For example, if your IPs are in Column B from rows 2 to 20, usegetRange("B2:B20"). -
Timeout Setting: The script uses a timeout of 5 seconds for each IP check. You can adjust this in the
timeout: 5parameter if needed. -
Handling HTTPS: If your devices support HTTPS, you might want to modify the URL to
"https://"instead of"http://". Be aware that this may affect the ability to connect if the device doesn't support HTTPS. -
Error Handling: The script assumes that any exception or non-success HTTP response code means the IP is offline. Adjust the logic if you need to handle specific cases differently.
Important Considerations
-
Quota Limits: Google Apps Script has daily quotas and limitations, especially concerning external requests (
UrlFetchApp). Monitoring a large number of IP addresses every 5 minutes may approach these limits. -
Script Runtime: Each execution of the script has a limited maximum runtime (usually 6 minutes). If you have many IP addresses to check, ensure the script completes within this time.
-
IPs Behind Firewalls: If the IP addresses are within a private network not accessible from the internet (e.g., behind a NAT or firewall), the script may not be able to reach them because the script runs on Google's servers.
Troubleshooting
-
Permissions Errors: If you encounter permissions errors, double-check that you've authorized the script correctly and that you're using an account with the necessary permissions.
-
Script Errors: Use the
Logsfeature in the Apps Script editor (View>Logs) to debug any errors that occur during script execution.
By following these steps, you should have a functioning system that checks the online status of IP addresses listed in your Google Sheet and updates the cell colors accordingly every 5 minutes.
Let me know if you need further assistance or have any questions!