Duplicate Column A on next spreadsheet according to column B - Google Apps Script / Google Sheets

0
votes

I am trying to Duplicate Column A Data(donations Received) to the next spreadsheet(Holding) according to the number placed in Column B.

So if Column A cell has Colgate & on the same line Column, B says 5 - it will copy Colgate 5 times on the next spreadsheet adding the date as well.

It needs to run this piece of code by checking a checkbox.

Please see link below to sample spreadsheet https://docs.google.com/spreadsheets/d/1VJZdR_FjBzhWvLnQwE7bgSKiCU82JuDkN1obPV0bOq0/edit?usp=sharing

Thank you in Advance

1
google-apps-scriptgoogle-sheets

1 Answers

1
votes
  • When the checkbox on the column "E" of the sheet "Donations Received" is clicked, you want to retrieve the values of the row and put Enter Company Name of Donor and Timestamp to the column "A" and "B" on the sheet of Holding, respectively.
    • In this case, you want to copy the values by the number of How Pallets were delivered (Do not use BOXES).

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

In this case, in order to run the script when the checkbox is clicked, the simple trigger of OnEdit event trigger is used.

  1. When a checkbox on the column "E" of the sheet "Donations Received" is clicked, the values of the row are retrieved.
  2. Create the values using the retrieved values.
  3. Put the created values to the last row in the sheet Holding.

Sample script:

Please copy and paste the following script to the container-bound script of your shared Spreadsheet. And please turn on the checkbox. In your current Spreadsheet, all checkboxes are checked. So at first, please uncheck the checkbox and check it. By this, the script is run.

function onEdit(e) {
  var range = e.range;
  var sheet = e.range.getSheet();
  if (sheet.getSheetName() == "Donations Received" && range.getColumn() == 5 && e.value == "TRUE") {
    var [DONATION, n,, DATE_RECEIVED] = e.range.offset(0, -4, 1, 4).getValues()[0];
    var values = [];
    for (var i = 0; i < n; i++) {
      values.push([DONATION, DATE_RECEIVED]);
    }
    var dstSheet = e.source.getSheetByName("Holding");
    dstSheet.getRange(dstSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
  }
}

Note:

  • Above sample script is for your shared Spreadsheet. So if you use it for other Spreadsheet, the script might not work. Please be careful this.
  • When you directly run the script at the script editor, in this case, an error occurs. Please be careful this.

References:

If I misunderstood your question and this was not the direction you want, I apologize.