Loop through a range of cells Google Sheets

Question

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:

it takes the name of the sheet where I want the results from cell A2 of sheet Private.
it takes the cell where I want the result from cell B1 of sheet Private.
we get the range by combining the sheet name in 1 and cell from 2
it takes the query that we need from the sheet that get from 1 and cell B4
makes a search in Gmail with the query from 4
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.

Cooper Cooper · Accepted Answer · 2017-08-01T16:55:46

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.

Loop through a range of cells Google Sheets

1 Answers