I'm a games design student and somewhat new to programming, especially java script. I'm doing a script for a google spreadsheet in which I am pulling rows from multiple spreadsheets using a closure (folder and subfolders) !! only if they have the cell on a certain column empty !!, and then send some emails. Now my problem is that I can't find a way to mark those previous empty cells as "sent" so I don't pull those rows in the next time I need to send these emails. I tried a couple of ways and to .setValues the same way I .getValues doesn't seem to work. Can anyone point me in the right direction on how I can setValue in those once empty cells after I do all the pulling emailing etc ? I can't seem to find any tutorial or forum post on this.

CodePudding user response：

    function addMenu()
{
  var menu = SpreadsheetApp.getUi().createMenu('Magic');
  menu.addItem('Run Script', 'setup');
  menu.addToUi();
}

function setup()
{
  combineData();
  makeLetters();
  sendLetters();
  markAsSent();
}


function combineData() 
{
  try 
  {
    const rootFolder = DriveApp.getFolderById('link');
    const combinedData = [];

    const _action = (file) => 
    {
      if (file.getMimeType() !== 'application/vnd.google-apps.spreadsheet') 
      {
        return;
      };
      const fileId = file.getId();
      let data = getDataFromFiles(fileId);

      if (data.length) 
      {
        const fileName = file.getName();
        data = data.map(row => [fileName].concat(row)); // prefix every row with the spreadsheet name
        combinedData.push(...data);
      }
    };
 
    processFilesInFolderRecursively_(rootFolder, _action);
    if (combinedData.length) 
    {
      SpreadsheetApp.getActive().getRange('Emails!A4:T')
        .clearContent()
        .offset(0, 0, combinedData.length, combinedData[0].length)
        .setValues(combinedData);

        var step1 = true;
        Logger.log("step1 "   step1);
    }
  } 
  catch (error) 
  {
    showAndThrow_(error);
  }
}
 
function getDataFromFiles(fileId) 
{
  const ss = SpreadsheetApp.openById(fileId);
  const filterRange = ss.getRange('Work!E4:V').getValues(); //V is where i need to mark as sent after the whole script is run

  const ids = filterRange.map(row => row[9]); // column N - AD
  const alreadySent = filterRange.map(row => row[17]); //column V - AD
  const data = ss.getRange('Work!E4:S').getValues();
  return data.filter((row, rowIndex) => String(ids[rowIndex]) && !String(alreadySent[rowIndex]));
}
 
function processFilesInFolderRecursively_(folder, action) 
{
  const files = folder.getFiles();
  while (files.hasNext()) 
  {
    action(files.next());
  }
  const subfolders = folder.getFolders();

  while (subfolders.hasNext()) 
  {
    processFilesInFolderRecursively_(subfolders.next(), action);
  }
}
function showAndThrow_(error) 
{
  var stackCodeLines = String(error.stack).match(/\d :/);
  if (stackCodeLines) 
  {
    var codeLine = stackCodeLines.join(', ').slice(0, -1);
  } 
  else 
  {
    codeLine = error.stack;
  }
  showMessage_(error.message   ' Code line: '   codeLine, 30);
  throw error;
}

This is the part that pulls the data from all the spreadsheets, and then the other 2 functions only make use of the cells pulled in order to make and send some emails(which contain a lot of things that I can't really post).

This closure was shown to me by someone else and I haven't fully understood how it works. I was only able to get the files from a folder and I needed help with the subfolders. Basically in that last function "markAsSent" is where I'm trying to make it so I mark all those other spreadsheet rows I took into this main one as sent. I feel like that would require running another check like this first one and take a long time but at this point I'm hopeless.

Edit: Ideally I would like to be able to mark them as sent in the other spreadsheets after everything is run(to make sure they are actually sent) but if the only option is to just mark them as sent after I pull them in at the beginning that's a good enough work around.

CodePudding user response：

Description

Might I suggest you change the following function as shown. This is the only place where the rows of Work!V containing "Sent" are know so you need to setValue() in this function

function getDataFromFiles(fileId) 
{
  const ss = SpreadsheetApp.openById(fileId);
  const filterRange = ss.getRange('Work!E4:V').getValues(); //V is where i need to mark as sent after the whole script is run

  const ids = filterRange.map(row => row[9]); // column N - AD
  const alreadySent = filterRange.map(row => row[17]); //column V - AD
  const dataRange = ss.getRange('Work!E4');
  const data = ss.getRange('Work!E4:S').getValues();
  return data.filter((row, rowIndex) => { if( String(ids[rowIndex]) && !String(alreadySent[rowIndex]) ) {
                                            dataRange.offset(rowIndex,17).setValue("Sent");
                                            return true;
                                          }
                                          return false;
                                        });                         
}