Loop script for all values of dropdown-CodePudding

I'm looking to make a script that cycles through a dropdown list and creates a pdf for each.

First I would like to check if B2 is not empty, then if so create pdf and change A2 to the next option until all are complete. I have a basic script but feel free to disregard!

function loopScript() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const interface = ss.getSheetByName("Interface");
  
  var folderID = "###GOOGLE DRIVE FOLDER ID###";
  var folder = DriveApp.getFolderById(folderID);
  
  const exportOptions =  'exportFormat=pdf&format=pdf' 
      '&size=A4'                      
      '&portrait=true'               
      '&scale=4'            
      '&fith=true&source=labnol'         
      '&top_margin=0.05'
      '&bottom_margin=0.05'
      '&left_margin=1.00'
      '&right_margin=0.25'
      '&sheetnames=false&printtitle=false'
      '&pagenumbers=false&gridlines=false'
      '&fzr=false'                       
      '&gid=125740569';                        
  
  var params = {method:"GET",headers:{"authorization":"Bearer "  ScriptApp.getOAuthToken()}};
 
  var response = UrlFetchApp.fetch(url exportOptions, params).getBlob();

  const nameFile =  "NAME OF FILE"   ".pdf" ;
  folder.createFile(response.setName(nameFile));
  DriveApp.createFile(response.setName(nameFile));
  
}

CodePudding user response：

I believe your goal is as follows.

You want to check the cell "B2". When the cell "B2" is not empty, you want to set the value of the dropdown list of cell "A2" to the next value of the list.
For example, when the dropdown list is Joe, Barry, Jane, Fred and the cell "A2" is Barry, you want to set the cell to Jane.

In this case, how about the following modified script?

Modified script:

From:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const interface = ss.getSheetByName("Interface");

To:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const interface = ss.getSheetByName("Interface");
if (interface.getRange("B2").isBlank()) return;
const range = interface.getRange("A2");
const values = [...new Set(range.getDataValidation().getCriteriaValues()[0].getValues().flat())];
const nextValue = values[values.indexOf(range.getValue())   1] || values[0];
range.setValue(nextValue);

In this modified script, when the cell "B2" is empty, the script is finished. When the cell "B2" is not empty, the script is run and the cell "A2" is updated and your script for creating the PDF file is run.

Note:

In above modified script, when the dropdown list is Joe, Barry, Jane, Fred and the cell "A2" is Fred, the value of Joe is set. If you want to change this, please modify the above script.
In your current script, url is not defined. Please be careful this.

References:

CodePudding user response：

Issue:

If I understand you correctly, you want to do the following:

For each dropdown in A2, check if the formula in B2 populates any values (based on data from sheet Data).
If any value is populated in B due to the formula, create a PDF file using the value of A2 for the file name (you have achieved this already).

Method 1:

In this case, I'd suggest the following workflow:

Retrieve an array with the accepted values from A2 dropdown (you can use the method used in Tanaike's answer).
Iterate through these values, and for each one, set the A2 value, using Range.setValue.
Call flush in order to update the data in B2 according to the current value in A2.
Check if B2 is blank (using Range.isBlank, for example).
If B2 is not blank, create the drive file.

function loopScript() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const interface = ss.getSheetByName("Interface");
  const range = interface.getRange("A2");
  const values = [...new Set(range.getDataValidation().getCriteriaValues()[0].getValues().flat())].filter(String);
  values.forEach(name => {
    range.setValue(name);
    SpreadsheetApp.flush();
    if (!interface.getRange("B2").isBlank()) {
      // CODE FOR CREATING FILE
    }
  });
}

Method 2:

In the previous method, setValue, flush, getRange and isBlank are used iteratively, greatly increasing the amount of calls to the spreadsheet. This is not the best practice, as it will slow down the script (see Minimize calls to other services), and this will get worse if there are more valid options for the dropdown.

Therefore, since the data this formula is using can be found in sheet Data, I'd suggest using that source data instead of the formula, in order to minimize the calls to the spreadsheet.

In this case, you could follow this workflow:

Get all data in Data at once using Range.getValues.
Get all valid options in the data validation from A2, as in method 1.
For each option, check if there's any row in Data that has this option in column A and a non-empty cell in B.
If there is some data for that option, create the file.

function loopScript() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const interface = ss.getSheetByName("Interface");
  const data = ss.getSheetByName("Data");
  const DATA_FIRST_ROW = 2;
  const dataValues = data.getRange(DATA_FIRST_ROW,1,data.getLastRow()-DATA_FIRST_ROW 1,2).getValues();
  const range = interface.getRange("A2");
  const values = [...new Set(range.getDataValidation().getCriteriaValues()[0].getValues().flat())].filter(String);
  values.forEach(name => {
    const optionValues = dataValues.filter(dataRow => dataRow[0] === name);
    const nonEmpty = optionValues.some(optionValue => optionValue[1] !== "");
    if (nonEmpty) {
      // CODE FOR CREATING FILE
    }
  });
}