סיכום נתונים ממספר גיליונות

רמת קידוד: מתחילים
משך: 5 דקות
סוג הפרויקט: פונקציה מותאמת אישית

מטרות

  • להבין מה הפתרון עושה.
  • הסבר על הפעולות של שירותי Apps Script בתוך הפתרון.
  • מגדירים את הסקריפט.
  • מריצים את הסקריפט.

מידע על הפתרון הזה

אם יש לכם נתונים בעלי מבנה דומה בכמה גיליונות בגיליון אלקטרוני, כמו מדדי תמיכת לקוחות של חברי צוות, תוכלו להשתמש בפונקציה המותאמת אישית הזו כדי ליצור סיכום של כל גיליון. הפתרון הזה מתמקד בבקשות תמיכה של לקוחות, אבל אפשר להתאים אותו לצרכים שלכם.

צילום מסך של הפלט של הפונקציה getSheetsData

איך זה עובד

הפונקציה בהתאמה אישית, שנקראת getSheetsData(), מסכם נתונים מכל גיליון בגיליון האלקטרוני על סמך העמודה Status של הגיליון. הסקריפט מתעלם מגיליונות שלא צריכים להיכלל בצבירה, כמו הגיליונות ReadMe ו-Summary.

שירותי Apps Script

הפתרון הזה משתמש בשירות הבא:

  • שירות גיליון אלקטרוני – הפונקציה מקבלת את הגיליונות שצריך לסכם ומספרת את מספר הפריטים שתואמים למחרוזת שצוינה. לאחר מכן, הסקריפט מוסיף את המידע המחושב לטווח ביחס למיקום שבו הפונקציה בהתאמה אישית הופעל בגיליון האלקטרוני.

דרישות מוקדמות

כדי להשתמש בדוגמה הזו, צריך את הדרישות המוקדמות הבאות:

  • חשבון Google (יכול להיות שחשבונות Google Workspace ידרשו אישור אדמין).
  • דפדפן אינטרנט עם גישה לאינטרנט.

הגדרת הסקריפט

לוחצים על הלחצן שלמטה כדי ליצור עותק של הגיליון האלקטרוני פונקציה מותאמת אישית לסיכום נתונים בגיליון אלקטרוני. פרויקט Apps Script של הפתרון הזה מצורף לגיליון האלקטרוני.
יצירת עותק

מריצים את הסקריפט

  1. בגיליון האלקטרוני שהעתקתם, עוברים לגיליון Summary.
  2. לוחצים על התא A4. הפונקציה getSheetsData() נמצאת בתא הזה.
  3. עוברים לאחד מגיליונות הבעלים ומעדכנים או מוסיפים נתונים לגיליון. תוכלו לנסות את הפעולות הבאות:
    • מוסיפים שורה חדשה עם פרטי כרטיס לדוגמה.
    • בעמודה סטטוס, משנים את הסטטוס של כרטיס קיים.
    • משנים את המיקום של העמודה סטטוס. לדוגמה, בגיליון Owner1, מעבירים את העמודה Status מעמודה C לעמודה D.
  4. עוברים אל הגיליון Summary ובודקים את טבלת הסיכום המעודכנת ש-getSheetsData() יצרה מהתא A4. יכול להיות שתצטרכו לסמן את התיבה בשורה 10 כדי לרענן את התוצאות ששמורות במטמון של הפונקציה בהתאמה אישית. Google שומרת בפונקציות בהתאמה אישית במטמון כדי לבצע אופטימיזציה של הביצועים.
    • אם הוספתם או עדכנתם שורות, הסקריפט יעדכן את מספרי הכרטיסים והסטטוסים.
    • אם שיניתם את המיקום של העמודה Status, הסקריפט עדיין יפעל כמצופה עם אינדקס העמודה החדש.

בדיקת הקוד

כדי לבדוק את הקוד של Apps Script לפתרון הזה, לוחצים על הצגת קוד המקור למטה:

הצגת קוד המקור

Code.gs

solutions/custom-functions/summarize-sheets-data/Code.js
// 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);
}

שינויים

אתם יכולים לערוך את הפונקציה בהתאם לצרכים שלכם. בהמשך מופיעה תוספת אופציונלית לרענון ידני של תוצאות של פונקציות בהתאמה אישית.

רענון תוצאות שנשמרו במטמון

בניגוד לפונקציות מובנות, Google שומרת פונקציות בהתאמה אישית במטמון כדי לבצע אופטימיזציה של הביצועים. המשמעות היא שאם משנים משהו בפונקציה בהתאמה אישית, כמו ערך שמחושב, יכול להיות שהיא לא תגרום לעדכון באופן מיידי. כדי לרענן את תוצאת הפונקציה באופן ידני, מבצעים את השלבים הבאים:

  1. כדי להוסיף תיבת סימון לתא ריק, לוחצים על הוספה > תיבת סימון.
  2. מוסיפים את התא עם תיבת הסימון כפרמטר של הפונקציה בהתאמה אישית, למשל getSheetsData(B11).
  3. מסמנים או מבטלים את הסימון של תיבת הסימון כדי לרענן את התוצאות של הפונקציה בהתאמה אישית.

תורמים

Google שומרת על הדוגמה הזו בעזרת מומחי הפיתוח של Google.

השלבים הבאים