עזרה בכתיבת SKRIPT לגוגל שיטס.

[מאסטר באקסל]

יש לי קוד שכבר כתוב, שמעביר נתונים מגיליון 1 לגיליון 2
בגיליון 1 זה נמחק ובגליון 2 זה נשאר תמיד
אבל פה יש בעיה, כל פעם שהוא כותב את הנתונים בגיליון 2,
הוא הולך לאותו תא (A2),
ואני רוצה שילך לתא הריק הראשון בעמודה A
ככה נראה הקוד:

function nisui2() {
  var spreadsheet = SpreadsheetApp.getActive(); 
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('גיליון 2'), true);
  spreadsheet.getRange('A2').activate();
  spreadsheet.getRange('\'גיליון 1\'!B7:L35').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('גיליון 1'), true);
  spreadsheet.getRange('D7:E35').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('D3:I3').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('D3').activate();
};

מה שרלוונטי לשאלה זה בין שורה 4 לחמש אני רוצה להכניס שורה כזו:

OFFSET(ROWS('A:A')-COUNTBLANK('A:A')+1,0)

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

הכל טוב ויפה, אבל כשאני מפעיל אותו אני מקבל הודעה ש ROWS אינו מוגדר,

וכאן אני שואל:

1. מה רע באיך שכתבתי?
2. איך כותבים את זה נכון?

[OdedDvir]

@מאסטר-באקסל אמר בעזרה בכתיבת SKRIPT לגוגל שיטס.:

מה רע באיך שכתבתי?

הניחוש שלי היא שבגלל אופן הפעולה שתיארת למעלה, התאים בגליון 1 שנמחקו - לא נחשבים ריקים, (ניקוי ערך לא הופך תא לריק, הוא הופך אותו לתא שמכיל מחרוזת ריקה) ואז הפונקציה COUNTBLANK מחזירה 0, מה שגורם לכך ש:

OFFSET(ROWS('A:A')-COUNTBLANK('A:A')+1,0) 
==
OFFSET(ROWS('A:A')-0+1,0) // זליגת תחום שורות

איך כותבים את זה נכון?

אפשרות א:
תשנה את הפונקציה clear כך שהתאים יימחקו לגמרי

אפשרות ב: תשתמש בפונקציה אחרת לחישוב השורה הראשונה הריקה: יש פונקציה מאוד מהירה שכתבו ב StackOverflow שמחזירה את השורה הראשונה הריקה, או הראשונה עם מחרוזת ריקה

function getFirstEmptyRowByColumnArray() {
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange('A:A');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return (ct+1);
}

עכשיו תכתוב:

OFFSET(getFirstEmptyRowByColumnArray(),0)

[מאסטר באקסל]

@OdedDvir אמר בעזרה בכתיבת SKRIPT לגוגל שיטס.:

@מאסטר-באקסל אמר בעזרה בכתיבת SKRIPT לגוגל שיטס.:

מה רע באיך שכתבתי?

הניחוש שלי היא שבגלל אופן הפעולה שתיארת למעלה, התאים בגליון 1 שנמחקו - לא נחשבים ריקים, (ניקוי ערך לא הופך תא לריק, הוא הופך אותו לתא שמכיל מחרוזת ריקה) ואז הפונקציה COUNTBLANK מחזירה 0, מה שגורם לכך ש:

OFFSET(ROWS('A:A')-COUNTBLANK('A:A')+1,0) 
==
OFFSET(ROWS('A:A')-0+1,0) // זליגת תחום שורות

איך כותבים את זה נכון?

אפשרות א:
תשנה את הפונקציה clear כך שהתאים יימחקו לגמרי

דבר ראשון, תודה!!
דבר שני לא זאת הסיבה כי אני רוצה תא ריק ראשון בגיליון 2 המחיקה היא בגיליון 1

@OdedDvir אמר בעזרה בכתיבת SKRIPT לגוגל שיטס.:

אפשרות ב: תשתמש בפונקציה אחרת לחישוב השורה הראשונה הריקה: יש פונקציה מאוד מהירה שכתבו ב StackOverflow שמחזירה את השורה הראשונה הריקה, או הראשונה עם מחרוזת ריקה
function getFirstEmptyRowByColumnArray() { var spr = SpreadsheetApp.getActiveSpreadsheet(); var column = spr.getRange('A:A'); var values = column.getValues(); // get all data in one call var ct = 0; while ( values[ct] && values[ct][0] != "" ) { ct++; } return (ct+1); }
עכשיו תכתוב:
OFFSET(getFirstEmptyRowByColumnArray(),0)

לקחתי את הפונקציה הזאת והיא לא עשתה שום דבר
גם אחרי ששמתי את ה- OFFSET
למה?

[OdedDvir]

@מאסטר-באקסל תוכל לרשום בדיוק את כל הקוד שעשית?

[מאסטר באקסל]

function nisui2() {
  var spreadsheet = SpreadsheetApp.getActive(); 
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('גיליון 2'), true);
  spreadsheet.getRange('A2').activate();
  spreadsheet.getCurrentCell().offset(rows('A:A')-countblank('A:A')+1,0), 0).activate();
  spreadsheet.getRange('\'גיליון 1\'!B7:L35').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('גיליון 1'), true);
  spreadsheet.getRange('D7:E35').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('D3:I3').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('D3').activate();
};

[OdedDvir]

@מאסטר-באקסל אמר בעזרה בכתיבת SKRIPT לגוגל שיטס.:

 spreadsheet.getCurrentCell().offset(rows('A:A')-countblank('A:A')+1,0), 0).activate();

תנסה במקום

spreadsheet.setActiveCell(spreadsheet.getDataRange().offset(spreadsheet.getLastRow()-1, 0, 1,  1));

[מאסטר באקסל]

@OdedDvir אמר בעזרה בכתיבת SKRIPT לגוגל שיטס.:

spreadsheet.setActiveCell(spreadsheet.getDataRange().offset(spreadsheet.getLastRow()-1, 0, 1, 1));

מדהים!!
עובד יפי!!
תודה!!

[מאסטר באקסל]

סליחה על הבורות,
אבל אפשר הסבר על השוני בנוסחאות,
ועל צורת הפעולה שלו?
(של ה- OFFSET)
בכדי שאני ידע להיזהר משגיאות בהפעלה שלו,
תודה

[OdedDvir]

@מאסטר-באקסל אמר בעזרה בכתיבת SKRIPT לגוגל שיטס.:

אבל אפשר הסבר על השוני בנוסחאות,
ועל צורת הפעולה שלו?
(של ה- OFFSET)

שני הפרמטרים האחרונים הם אופציונאליים, ומציינים את טווח היעד הנבחר (כמה שורות וכמה עמודות)
אם משמיטים אותם, הפונקציה תחזיר טווח הזהה לטווח הפעיל.
למשל:
אם הטווח הפעיל הוא לא תא אחד, אלא קבוצת תאים, למשל A1:B2 (=4 תאים) הפונקציה מחזירה טווח של 4 תאים:

offset(1,1) // == B2:C3

אלא אם כן תציין בפירוש שברצונך לקבל רק תא אחד

offset(1,1,1,1) // == B2

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

[מאסטר באקסל]

תודה
אבל התכוונתי לדעת על הפרמטר הראשון של ירידת שורות,
איך הוא מוצא את התא הראשון הריק?

[OdedDvir]

@מאסטר-באקסל
מסתבר שיש פונקציה מובנית ()getLastRow שמחזירה את מספר השורה האחרונה הריקה. הפחתתי 1 כי אתה משתמש בהיסט (offset) וצריך לזוז שורה פחות

[OdedDvir]

אחרי קצת התבוננות, ראיתי שבכלל אין צורך בשימוש בOFFSET, ואפשר לקפוץ ישר לתא הרצוי:

spreadsheet.setActiveRange(spreadsheet.getRange(spreadsheet.getLastRow()+1, 1, 1, 1));