如何用Google Apps Script实现谷歌表格自动打开URL及时间填充？

Problem Context #

I maintain a Google Sheet where I manually set the start time for the first row, open its associated URL, then mark Y/N in the Purchasable column. Once that mark is made, I want:

1. The end time column for that row to auto-populate
2. The URL from the next row to open automatically, so I can repeat the workflow without extra clicks

I’ve got part of the script working to handle the timestamp updates when the Purchasable column is edited:

if(r.getColumn()==4){ 
  var endCell=r.offset(0,4); 
  if(endCell.getValue()==='') 
  var date=Utilities.formatDate(new Date(),timezone,timestamp_format); 
  endCell.setValue(date); 
  var startCell=r.offset(1,-1); 
  startCell.setValue(date); 
}

But I can’t figure out how to auto-launch the next row’s URL. Is this possible with Apps Script?

Solution #

Absolutely! The catch is that server-side Apps Script code can’t directly open a URL in your browser, but we can use a tiny client-side HTML dialog to trigger the browser to open the link automatically. Here’s the full, updated script that handles both the timestamp logic and auto-open functionality:

function onEdit(e) {
  // Configure your settings here
  const timezone = Session.getScriptTimeZone(); // Uses your script's timezone, adjust if needed
  const timestampFormat = "yyyy-MM-dd HH:mm:ss";
  const purchasableColumn = 4; // Column D, change to match your sheet
  const urlColumn = 2; // Column B, change to your URL column's index
  const startTimeColumn = purchasableColumn - 1; // Column C, adjust if needed
  const endTimeColumn = purchasableColumn + 4; // Column H, adjust if needed

  const editedRange = e.range;
  const sheet = editedRange.getSheet();
  const currentRow = editedRange.getRow();

  // Only run logic if we're editing the Purchasable column
  if (editedRange.getColumn() === purchasableColumn) {
    const endTimeCell = sheet.getRange(currentRow, endTimeColumn);

    // Populate end time if it's empty
    if (endTimeCell.getValue() === '') {
      const currentTimestamp = Utilities.formatDate(new Date(), timezone, timestampFormat);
      endTimeCell.setValue(currentTimestamp);

      // Set start time for the next row
      const nextRow = currentRow + 1;
      const nextStartTimeCell = sheet.getRange(nextRow, startTimeColumn);
      nextStartTimeCell.setValue(currentTimestamp);

      // Grab the next row's URL
      const nextUrlCell = sheet.getRange(nextRow, urlColumn);
      const nextUrl = nextUrlCell.getValue();

      // Open the URL only if it's a valid web link
      if (nextUrl && nextUrl.startsWith('http')) {
        // Create a tiny, self-closing dialog to trigger the URL open
        const html = HtmlService.createHtmlOutput(`
          <script>
            window.open('${nextUrl}', '_blank');
            google.script.host.close();
          </script>
        `).setWidth(10).setHeight(10);

        SpreadsheetApp.getUi().showModalDialog(html, 'Opening next URL...');
      }
    }
  }
}

Important Details to Adjust: #

• Column Indices: Update purchasableColumn, urlColumn, startTimeColumn, and endTimeColumn to match the actual column numbers in your sheet (column A = 1, B = 2, etc.).
• Timezone/Format: Tweak timezone and timestampFormat if you need a different time zone or date display style.
• Error Handling: The script checks if the next URL starts with http to avoid trying to open empty cells or invalid text, which would cause errors.

Setup Steps: #

1. Open your Google Sheet, go to Extensions > Apps Script.
2. Replace any existing code with the script above.
3. Adjust the configuration variables at the top to match your sheet’s structure.
4. Save the project, then close the script editor.
5. Test by editing the Purchasable column: the end time should populate, the next row’s start time should set, and the next URL should open automatically in a new tab.

内容的提问来源于stack exchange，提问作者Rahul Singh