HOW TO USE: Global Array in Function with Loop/While (JavaScript/GAS)

Question

I would like to use a global array / variable in my function. The function should be executed as long as IDS are in the array.

In the variable "var files = [...];" there are for example two IDS, depending on how many files are in the folder.

var files = ['16EdsAx', '16wQxxIc'];

var files = [];

function getListOfId(){

  var folderId = "11tjb_odTJ2E_ez";
  var filesN = DriveApp.getFolderById(folderId).getFiles();

  while (filesN.hasNext()) files.push(filesN.next().getId());
  //console.log(files);
}

---

Don't be intimidated, these two functions only read the DOCs documents and write them into the corresponding cell.

function getDocItems(docID, identifier){
  const body = DocumentApp.openById("13TlciLOZV").getBody(); // >>> The IDS from the array should be used here <<<<
  const docText = body.getText();
  
  //Check if search characters are to be included. 
  let startLen =  identifier.start_include ? 0 : identifier.start.length;
  let endLen = identifier.end_include ? 0 : identifier.end.length;
  
  //Set up the reference loop
  let textStart = 0;
  let doc = docText;
  let docList = [];
  
  //Loop through text grab the identifier items. Start loop from last set of end identfiers.
  while(textStart > -1){ 
    let textStart = doc.indexOf(identifier.start);
    
    if(textStart === -1){
      break;  
    }else{
      
      let textEnd = doc.indexOf(identifier.end) + identifier.end.length;
      let word = doc.substring(textStart,textEnd);
      
      doc = doc.substring(textEnd);
      
      docList.push(word.substring(startLen,word.length - endLen));
    };
  };
  //return a unique set of identifiers. 
  return [...new Set(docList)];
};


//The chewy conversation
function runsies(){
  const docID = "13TlciLOZV";    // >>> The IDS from the array should be used here <<<<
  const identifier = {
    start: `ISIN: `,
    start_include: false,
    end: `VERRECHNUNGSKONTO`,
    end_include: false
  };
  let results = getDocItems(docID, identifier);
  //var commaAdd = results.join("''");
  //console.log(results);
  
  const ss = "17a55HCwlO5uF8gkXpG";//The spreadsheet ID
  const sheet = "Stock_Data";//The sheet tab name
  var activeSheet = SpreadsheetApp.getActiveSheet();
  let importToSpredsheet = SpreadsheetApp.openById(ss).getSheetByName(sheet);
  const range = activeSheet.getRange(6,1,results.length,1);
  range.setValue(results);
};

---

Here you can find the tutorial where I got this code from. HERE

I always used the exact docs id in the code. But now I would like to use the ids from the array from the getListOfId () function. The information from the files should all be in different cells, ideally all in column A one below the other.

So my questions are:

How can I refer to the IDS in the other two functions?

The function should be repeated until all IDS have been used and all files have been read out and entered in the spreadsheet, but how?

Answer 1

I believe your goal as follows.

• You want to retrieve the Google Document IDs from the function of getListOfId.
  • In this case, the IDs returned from getListOfId are always the file IDs of Google Document.
• You want to use the file IDs to docID of let results = getDocItems(docID, identifier); in the function of runsies.
• You want to put the values retrieved from the function of getDocItems to the sheet of Stock_Data in the Google Spreadsheet.

Modification points:

• In this case, I would like to propose the following flow.
  1. Retrieve the file IDs from getListOfId.
     • In this modification, the file IDs retrieved from getListOfId are used in runsies.
  2. Put the file IDs to getDocItems using a loop.
  3. Put the result values to the Spreadsheet.
• When I saw your script for putting values to the Spreadsheet, the values are put to the active sheet. If you want to put the values to the sheet of Stock_Data in the Google Spreadsheet of const ss = "17a55HCwlO5uF8gkXpG";, it is required to modify the script.
• And also, in your script, by const range = activeSheet.getRange(6,1,results.length,1); and range.setValue(results);, the 1st element in the array of results is put the number of times of the length of results from the cell "A6". When you want to put the values from the row 6, it is required to modify the script.

When above points are reflected to your script, it becomes as follows.

Modified script:

getListOfId()

Please set your folder ID.

function getListOfId(){
  var folderId = "###"; // Please set your folder ID.
  var filesN = DriveApp.getFolderById(folderId).getFiles();
  var files = [];
  while (filesN.hasNext()) files.push(filesN.next().getId());
  return files;
}

runsies()

Please set your Spreadsheet ID.

function runsies(){
  const docIDs = getListOfId();  // Here, the file IDs are retrieved from `getListOfId`.
  const identifier = {
    start: `ISIN: `,
    start_include: false,
    end: `VERRECHNUNGSKONTO`,
    end_include: false
  };
  if (docIDs.length == 0) return;
  const results = docIDs.map(id => getDocItems(id, identifier));  // Here, the retrieved file IDs are used in a loop.
  const ss = "###"; // Please set your Spreadsheet ID.
  const sheetName = "Stock_Data"; //The sheet tab name
  const sheet = SpreadsheetApp.openById(ss).getSheetByName(sheetName);
  const range = sheet.getRange(sheet.getRange(6,1).isBlank() ? 6 : sheet.getLastRow() + 1,1,results.length,results[0].length);
  range.setValues(results);
}

• In this case, when docIDs has not file IDs, the script is stopped.
• In this modified script, from your script, the retrieved values results are put from the row 6 on the sheet of Stock_Data in the Google Spreadsheet const ss = "###". When the values has already been existing from the row 6, the values are appended.

getDocItems(docID, identifier)

From:

const body = DocumentApp.openById("13TlciLOZV").getBody();

To

const body = DocumentApp.openById(docID).getBody();

Note:

• Please use this modified script with enabling V8 runtime.
• If above modification is not the result you expect, can you show the whole script and the detail of your goal? By this, I would like to confirm it.

References:

• map()
• setValues(values)