Home>
goal

I'm thinking of creating something like the figure below.

If the black box is A, B, C, D from the left,
Get each number from another table,
It is a process to record every day on the line of the corresponding date.

Why did you ask here

I tried a lot with the function, but thought it was too complicated to be realistic.
Also, I asked my engineer to create it, but I was told that it was difficult.
I'm sorry I can't tell how difficult this process is,
Thanks for your cooperation.

Edit/Addition request

For example, for A, B, C, D of 2020/10/20 in another table, the 2020/10/20 row of the yellow spreadsheet presented in the question text,
You set it to A, B, C, D. The process I want to execute is said to be "additional", but what kind of process is the additional process here?
When it comes to "posting", it feels like it's overwritten, but when it comes to "addition", is it added horizontally or is it added up?

∟ I'm sorry I couldn't explain it well!

A: Apple, B: None, C: Grape, D: Oyster
(Originally, it will be our product name.)

Column 1 The column I want to get
Line 1 Apple
Line 2 None
Line 3 Grape
Line 4 Oysters
Line 5 Apple
Line 6 Apple
Line 7 Grape

As mentioned above, there is a table in a separate tab, and it feels like getting some columns of it every day.

In this case, if you run the process,
From the left, [3,1,2,1] is entered in the line of the processing date and the corresponding date.
(Apples: 3 cases, None: 1 case, Grape: 2 cases, Kaki: 1 case)

Also, get each number in the column I want to get the next day,
I assume that it will be repeated every day.

(* Regardless of whether the column I want to get is updated)

Therefore, I think that it is different from overwriting and totaling.
(I noticed that it was not "additional" when I wrote it.)

I want to ignore the number of cases on the previous day and always get and record the number of cases on that day.

Finally, load this in the Google data portal to create a daily reserve graph.

The details are unknown, but it doesn't seem like a difficult process to reach the goal with GAS.
However, I think it's a work request and deprecated to ask for that code here.

∟ There was sweetness. I'm sorry.
Is it possible if there is a hint that such a method can solve the problem?
Based on that, I will try to create it by trial and error.

Or the questioner's company engineer says "difficult"
In fact, there may be requirements that are not mentioned in the question text.
Therefore, it was judged that it would be difficult to program with GAS.

∟ I think there are some places that I haven't explained well.
Also, since it is a sideways task, I think it also means that it is difficult to respond immediately.

By the way, if you have a question about GAS, please add it because it has a tag of "Google Apps Script".
By attaching it, it will be noticed by many other readers and respondents.

∟ Added!

  • Answer # 1

    There is no special processing, and you should be able to create a program that meets your needs in the following flow.

    From Sheet A on which today's data is entered, loop as much as the data exists and calculate the number of cases for each product name.

    Based on the date and data obtained in 1., find the row of the corresponding date on another sheet B.

    In the row of sheet B that matches 2., post the number in the column for each product name.
    If there is no data for today on Sheet A, copy the data from the previous row on Sheet B and post it.

    However, at present, it may be too much work for the questioner, so I will introduce the code as a sample.

    Regarding the description added to [2020/10/30 12:00]:

    As mentioned above, there is a table in a separate tab, and it feels like getting some columns of it every day.

    This is a Google Apps Script sample code that calculates the number of items for each product name from a sheet that conforms to this table.

    function test33 () {
      // Get the "TEST33" sheet from the currently active spreadsheet
      let ss = SpreadsheetApp.getActiveSpreadsheet ();
      let sheet = ss.getSheetByName ("TEST33");
      // Loop between lines 1-7 to find the quantity for each item.
      var item_num = [];
      for (let i = 1;i<= 7;i ++) {
        let range = sheet.getRange (i, 2);
        let values ​​= range.getValues ​​();
        Logger.log (values ​​[0] [0]);
        let k = values ​​[0] [0];
        if (k in item_num) {
          item_num [k] + = 1;
        } else {
          item_num [k] = 1;
        }
      }
      // Get the current date and time (local time)
      let now = new Date ();
      let year = now.getFullYear ();
      let month = now.getMonth () + 1;
      let date = now.getDate ();
      // Output the current date on the 9th line
      let s = year + "year" + month + "month" + date + "day";
      sheet.getRange (9, 2) .setValue (s);
      // Output the aggregation result from the 11th line
      let i = 11;
      for (let k in item_num) {
        Logger.log ('item ='+ k +', num ='+ item_num [k]);
        sheet.getRange (i, 2) .setValue (k);
        sheet.getRange (i, 3) .setValue (item_num [k]);
        ++ i;
      }
    }

    When this is executed, it will look like the image below on the "TEST33" sheet.

    The sample is verbose and has some parts written in a straightforward manner. I'm accessing each cell in a loop, but it can be slow when it comes to large amounts of data. In such a case, you can access at once by specifying the range. (The method is omitted here)

    See the reference for official information about GAS-specific spreadsheet classes.
    Spreadsheet Service-Google Apps Script

    This question is related to Google Apps Script, which is also related to Google Spreadsheets, but the basics and ideas of the program flow should be applicable to Excel/VBA. If you can write a program on your own, the application will be effective. Good luck.

  • Answer # 2

    Sawa-san was the hint for the solution, but dodox86-san helped me many times, so
    I made it the best answer.

    The process that was actually solved

    function number () {
      function getUsedRowCount (sheet, rowName) {
        var values ​​= sheet.getRange (rowName +':' + rowName) .getValues ​​();
        return values.filter (String) .length;
    }
    var ss_copyFrom = SpreadsheetApp.openById ('Spreadsheet ID');
    var ss_copyTo = SpreadsheetApp.openById ('Spreadsheet ID');
    var sheet_copyFrom = ss_copyFrom.getSheetByName ('copy source');
    var sheet_copyTo = ss_copyTo.getSheetByName ('copy and paste destination');
    var copyValue = sheet_copyFrom.getRange ('B3: G3'). getValues ​​();
    var targetRow = getUsedRowCount (sheet_copyTo,'A') + 1;
    sheet_copyTo.getRange ('A' + targetRow +': F'+ targetRow) .setValues ​​(copyValue);
    }


    I feel like this.

    The counting itself is done with countif, and it is copied with GAS and pasted in another tab!

Related articles