خلاصه کردن داده ها از چندین برگه

سطح کدنویسی : مبتدی
مدت زمان : 5 دقیقه
نوع پروژه : تابع سفارشی

اهداف

• درک کنید که راه حل چه کاری انجام می دهد.
• آنچه را که سرویس های Apps Script در راه حل انجام می دهند، بدانید.
• اسکریپت را تنظیم کنید
• اسکریپت را اجرا کنید.

در مورد این راه حل

اگر داده‌های ساختاری مشابهی در چندین صفحه در یک صفحه گسترده دارید، مانند معیارهای پشتیبانی مشتری برای اعضای تیم، می‌توانید از این تابع سفارشی برای ایجاد خلاصه‌ای از هر صفحه استفاده کنید. این راه حل بر روی بلیط های پشتیبانی مشتری تمرکز دارد، اما شما می توانید آن را مطابق با نیازهای خود سفارشی کنید.

چگونه کار می کند

تابع سفارشی که getSheetsData() نامیده می شود، داده های هر صفحه در صفحه گسترده را بر اساس ستون وضعیت یک برگه خلاصه می کند. این اسکریپت برگه هایی را که نباید در انبوه گنجانده شود، نادیده می گیرد، مانند برگه های ReadMe و Summary .

خدمات اسکریپت برنامه ها

این راه حل از سرویس زیر استفاده می کند:

• سرویس صفحه گسترده - برگه هایی را دریافت می کند که باید خلاصه شوند و تعداد مواردی را که با یک رشته مشخص مطابقت دارند را می شمارد. سپس، اسکریپت اطلاعات محاسبه شده را به محدوده ای نسبت به جایی که تابع سفارشی در صفحه گسترده فراخوانی شده است، اضافه می کند.

پیش نیازها

برای استفاده از این نمونه به پیش نیازهای زیر نیاز دارید:

• یک حساب Google (حساب‌های Google Workspace ممکن است به تأیید سرپرست نیاز داشته باشند).
• یک مرورگر وب با دسترسی به اینترنت.

اسکریپت را تنظیم کنید

روی دکمه زیر کلیک کنید تا یک کپی از صفحه گسترده عملکرد سفارشی داده های صفحه گسترده خلاصه کنید. پروژه Apps Script برای این راه حل به صفحه گسترده پیوست شده است.
یک کپی تهیه کنید

اسکریپت را اجرا کنید

1. در صفحه‌گسترده کپی شده خود، به صفحه خلاصه بروید.
2. روی سلول A4 کلیک کنید. تابع getSheetsData() در این سلول است.
3. به یکی از برگه های مالک بروید و داده ها را به صفحه به روز کنید یا به آن اضافه کنید. برخی از اقداماتی که می توانید امتحان کنید شامل موارد زیر است:
   • یک ردیف جدید با نمونه اطلاعات بلیط اضافه کنید.
   • در ستون وضعیت ، وضعیت یک بلیط موجود را تغییر دهید.
   • موقعیت ستون Status را تغییر دهید. به عنوان مثال، در برگه Owner1 ، ستون Status را از ستون C به ستون D منتقل کنید.
4. به برگه Summary بروید و جدول خلاصه به روز شده ای را که getSheetsData() از سلول A4 ایجاد کرده است مرور کنید. ممکن است لازم باشد چک باکس ردیف 10 را علامت بزنید تا نتایج ذخیره شده در حافظه پنهان تابع سفارشی بازخوانی شود . گوگل توابع سفارشی را برای بهینه سازی عملکرد ذخیره می کند.
   • اگر ردیف‌هایی را اضافه کرده یا به‌روزرسانی کرده‌اید، اسکریپت بلیط و وضعیت را به‌روزرسانی می‌کند.
   • اگر موقعیت ستون وضعیت را جابجا کرده باشید، اسکریپت همچنان همانطور که در فهرست ستون جدید در نظر گرفته شده است کار می کند.

کد را مرور کنید

برای بررسی کد Apps Script برای این راه حل، روی مشاهده کد منبع در زیر کلیک کنید:

مشاهده کد منبع

Code.gs

solutions/custom-functions/summarize-sheets-data/Code.js

در GitHub مشاهده کنید

// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/custom-functions/summarize-sheets-data

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

/**
 * Gets summary data from other sheets. The sheets you want to summarize must have columns with headers that match the names of the columns this function summarizes data from.
 * 
 * @return {string} Summary data from other sheets.
 * @customfunction
 */

// The following sheets are ignored. Add additional constants for other sheets that should be ignored.
const READ_ME_SHEET_NAME = "ReadMe";
const PM_SHEET_NAME = "Summary";

/**
 * Reads data ranges for each sheet. Filters and counts based on 'Status' columns. To improve performance, the script uses arrays 
 * until all summary data is gathered. Then the script writes the summary array starting at the cell of the custom function.
 */
function getSheetsData() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheets = ss.getSheets();
  let outputArr = [];

  // For each sheet, summarizes the data and pushes to a temporary array.
  for (let s in sheets) {
    // Gets sheet name.
    let sheetNm = sheets[s].getName();
    // Skips ReadMe and Summary sheets.
    if (sheetNm === READ_ME_SHEET_NAME || sheetNm === PM_SHEET_NAME) { continue; }
    // Gets sheets data.
    let values = sheets[s].getDataRange().getValues();
    // Gets the first row of the sheet which is the header row.
    let headerRowValues = values[0];
    // Finds the columns with the heading names 'Owner Name' and 'Status' and gets the index value of each.
    // Using 'indexOf()' to get the position of each column prevents the script from breaking if the columns change positions in a sheet.
    let columnOwner = headerRowValues.indexOf("Owner Name");
    let columnStatus = headerRowValues.indexOf("Status");
    // Removes header row.
    values.splice(0,1);
    // Gets the 'Owner Name' column value by retrieving the first data row in the array.
    let owner = values[0][columnOwner];
    // Counts the total number of tasks.
    let taskCnt = values.length;
    // Counts the number of tasks that have the 'Complete' status.
    // If the options you want to count in your spreadsheet differ, update the strings below to match the text of each option.
    // To add more options, copy the line below and update the string to the new text.
    let completeCnt = filterByPosition(values,'Complete', columnStatus).length;
    // Counts the number of tasks that have the 'In-Progress' status.
    let inProgressCnt = filterByPosition(values,'In-Progress', columnStatus).length;
    // Counts the number of tasks that have the 'Scheduled' status.
    let scheduledCnt = filterByPosition(values,'Scheduled', columnStatus).length;
    // Counts the number of tasks that have the 'Overdue' status.
    let overdueCnt = filterByPosition(values,'Overdue', columnStatus).length;
    // Builds the output array.
    outputArr.push([owner,taskCnt,completeCnt,inProgressCnt,scheduledCnt,overdueCnt,sheetNm]);
  }
  // Writes the output array.
  return outputArr;
}

/**
 * Below is a helper function that filters a 2-dimenstional array.
 */
function filterByPosition(array, find, position) {
  return array.filter(innerArray => innerArray[position] === find);
}

اصلاحات

شما می توانید تابع سفارشی را تا جایی که می خواهید متناسب با نیاز خود ویرایش کنید. در زیر یک افزونه اختیاری برای بازخوانی دستی نتایج عملکرد سفارشی وجود دارد.

نتایج ذخیره شده را بازخوانی کنید

برخلاف توابع داخلی، گوگل توابع سفارشی را برای بهینه سازی عملکرد در حافظه پنهان ذخیره می کند. این بدان معنی است که اگر چیزی را در تابع سفارشی خود تغییر دهید، مانند مقداری که در حال محاسبه است، ممکن است فوراً مجبور به به روز رسانی نشود. برای بازخوانی دستی نتیجه عملکرد، مراحل زیر را انجام دهید:

1. با کلیک کردن روی Insert > Checkbox یک کادر انتخاب را به یک سلول خالی اضافه کنید.
2. سلولی را که دارای چک باکس است به عنوان پارامتر تابع سفارشی اضافه کنید، به عنوان مثال، getSheetsData(B11) .
3. برای بازخوانی نتایج عملکرد سفارشی، کادر انتخاب را علامت بزنید یا علامت آن را بردارید.

مشارکت کنندگان

این نمونه توسط گوگل با کمک کارشناسان توسعه دهنده گوگل نگهداری می شود.

مراحل بعدی

• توابع سفارشی در Google Sheets
• گسترش Google Sheets