CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Dan Nunu
Dan Nunu

Reputation: 11

Loop through a range of cells Google Sheets

First of all, I'm new to coding - so the script that I have I combined it from a couple different scripts and it seems to work, but I would like to simplify it.

I have such a script:

function count() {

  var Month = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Private').getRange('A2').getValues();
    var PCC = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Private').getRange('B1').getValues();
    var PCCQ = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(Month).getRange(PCC);
    var PCCS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(Month).getRange('B4').getValue();
    var PhoneCC = GmailApp.search(PCCS);

PCCQ.setValue(PhoneCC.length);

}

What it does:

  1. it takes the name of the sheet where I want the results from cell A2 of sheet Private.
  2. it takes the cell where I want the result from cell B1 of sheet Private.
  3. we get the range by combining the sheet name in 1 and cell from 2
  4. it takes the query that we need from the sheet that get from 1 and cell B4
  5. makes a search in Gmail with the query from 4
  6. sets the value of the cell from point 3 as the length of the search from point 5

However I have a range of cells for point 2 that I need this to work through, let's say B1:B70.

Is there a way to adjust this script to do this?

Will be very thankful.

Upvotes: 1

Views: 984

Answers (1)

Cooper
Cooper

Reputation: 64100

I'd make the following changes. Keep going.

    var Month = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Private').getRange('A2').getValue();
    var PCC = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Private').getRange('B1').getValue();
    var PCCQ = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Month').getRange(PCC);//The range is not sensible unless it is a named range but the getRange does not work with named ranges.
    var PCCS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Month').getRange('B4').getValue();
    var PhoneCC = GmailApp.search(PCCS); //how do you plan to display your results.

Upvotes: 2

Related Questions