Google Sheets跨工作表条件格式设置、跨表数据同步及与Data Studio日程联动技术咨询

Hey there! Let's break down each of your requirements with step-by-step solutions tailored to Google Sheets and Looker Studio (formerly Data Studio):

Same Google Sheets File (Between Sheets) #

• Select the range with existing conditional formatting in your source sheet (e.g., Sheet1).
• Right-click and choose Copy, or use Ctrl+C (Windows) / Cmd+C (Mac).
• Navigate to your target sheet (e.g., Sheet2), select the range you want to apply the formatting to.
• Right-click, hover over Paste special, then select Paste format only.
• Pro tip: If your conditional formatting uses custom formulas, double-check cell references—adjust relative/absolute markers (like adding $ where needed) to ensure the rules apply correctly to the new range.

Different Google Sheets Files #

If you need to move formatting across separate files, a quick Google Apps Script will do the trick:

1. Open your target Google Sheet, go to Extensions > Apps Script.
2. Replace the default code with this snippet:

function copyConditionalFormatting() {
  const sourceFileId = "YOUR_SOURCE_SHEET_FILE_ID"; // Replace with your source file ID
  const sourceSheetName = "SourceSheet"; // Replace with your source sheet name
  const targetSheetName = "TargetSheet"; // Replace with your target sheet name
  
  const sourceSheet = SpreadsheetApp.openById(sourceFileId).getSheetByName(sourceSheetName);
  const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName);
  
  // Pull all conditional format rules from the source
  const rules = sourceSheet.getConditionalFormatRules();
  // Apply rules to the target sheet
  targetSheet.setConditionalFormatRules(rules);
}

3. Replace the placeholder values with your actual file ID and sheet names.
4. Click the run button (▶️), authorize the script when prompted.
5. Your source sheet’s conditional formatting rules will now be applied to the target sheet.

To display walk-in patients in time slots between 7:30 AM and 5:00 PM, follow these steps:

1. Connect Your Google Sheets Data:

   • Open Looker Studio, create a new report, and select Google Sheets as your data source.
   • Select your reference sheet, and confirm the time column is recognized as a Time data type (adjust this in the data source settings if it’s labeled as text).

2. Create a Time Slot Calculated Field:

   • In your data source, go to Add a Field and use a CASE statement to group times into 30-minute slots (adjust intervals to match your needs):

   CASE
     WHEN TIME(time) BETWEEN TIME("07:30:00") AND TIME("08:29:59") THEN "7:30–8:30"
     WHEN TIME(time) BETWEEN TIME("08:30:00") AND TIME("09:29:59") THEN "8:30–9:30"
     -- Continue adding slots up to the end of your schedule
     WHEN TIME(time) BETWEEN TIME("16:30:00") AND TIME("17:00:00") THEN "16:30–17:00"
     ELSE "Outside Schedule"
   END
   

   • Name the field Time Slot and save it.

3. Build the Schedule Data Frame:

   • Add a Table component to your report.
   • Set Time Slot as the Dimension (rows) and Patient Name as the Metric (set aggregation to None to display individual names directly).
   • Add two Filters to the table:
     • Filter 1: Label equals walk-in
     • Filter 2: time is between 07:30:00 and 17:00:00
   • Optional: Customize the table’s styling (e.g., alternating row colors, bold headers) to make it look like a clean schedule grid.

Same Google Sheets File #

This is super straightforward—use a simple cell reference:

• In the target cell on Sheet2 (e.g., Sheet2!B2), enter the formula:

  =Sheet1!A1
  

  Replace Sheet1!A1 with the actual cell reference from your source sheet. Any changes to the source cell will automatically update in the target cell in real time.

Different Google Sheets Files #

Use the IMPORTRANGE function to pull data across separate files:

• In your target sheet’s cell, enter:

  =IMPORTRANGE("YOUR_SOURCE_SHEET_FILE_ID", "Sheet1!A1")
  

  • Replace YOUR_SOURCE_SHEET_FILE_ID with the ID from your source sheet’s URL (the string between /d/ and /edit).
  • You’ll be prompted to Allow access to connect the two sheets—click the button to authorize, and the data will sync automatically.

内容的提问来源于stack exchange，提问作者Emma Bourdeau