seo Question posted in
Some basic information to get you started with this can be found here.

Move Specific Rows depending on Filtering Keywords within unknown amount of rows using Google Sheets Apps Scripts – SEO

I do SEO, and therefore I have a lot of keywords flowing around in different spreadsheets. I’d like a way to filter these into seperate sheets based on specific filters, but I can’t for the life of me, figure out how to do this in Google Apps Script.

Criteria I set myself for this to work out:

  • A list of strings and their corresponding volumes are entered in column 1+2.
  • A list of filter-words are written in column 3.
  • The script has to create a new sheet for each of the filter words and move the strings + volumes into these different sheets if the string contains a filter word.

Example:
Filter words: Apple, Banana, Pineapple

String: "The Apple Was Big", Volume: "100"

The script would move the string and volume into the sheet called "Apple" on row 1

(Beware, I’m in no means experienced in coding)
I believe you can use the following structure:

for(let i = 0; i <= column3RowAmount; i++){ //Run as long as there are more filter words
   create(column3Row[i]); //create a new sheet with the name of the filter word
   for(let j = 0; j <= column1RowAmount; j++){ //Run as long as there are more keywords
      if(column1Row[j].indexOf(column3Row[i]) >= 0){ //If the Row in column 1 contains the filter word
         column1Row[j].moveToSheet(column3Row[i]); // Make sure not to move Column 3, but only 1+2
      }
   }
}

Example sheet: https://docs.google.com/spreadsheets/d/15YIMyGmmfZdy094gwuJNxFmTd8h7NOLnA8KevZrGtdU/edit?usp=sharing

Tags:

2

Answers


  1. 0

    Explanation:

    • Your goal is to create a sheet for every filter-word in column C. Then copy the data in columns A, B but only the rows that include the filter-word to the corresponding sheet.

    • For starters, you need to get the filter-word list. You can get the full range of column C and filter out the empty cells:

      const sh_names = sh.getRange('C1:C').getValues().flat().filter(r=>r!='');

    • Similarly, you need to get the data in columns A and B:

      const data = sh.getRange('A1:B'+sh.getLastRow()).getValues();

    • The next step is to iterate over sh_names and for every element / filter-word, check if a sheet with that name exists. If it does not exist, then create a sheet with that name, if it exists then skip the creation part:

        if(!ss.getSheetByName(s)){
  ss.insertSheet().setName(s);}

    • The next step is to filter data on the rows that include the filter-word:

      let f_data = data.filter(r=>r[0].includes(s));

    • Finally, check if the length of the data is bigger than 0, otherwise there is not data to use and set the values of data to the corresponding sheet:

      sheet.getRange(sheet.getLastRow()+1,1,f_data.length,f_data[0].length).setValues(f_data)

    Solution

    function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Ark1');
  const filter_sh = ss.getSheetByName('Filter');
  const data = sh.getRange('A1:B'+sh.getLastRow()).getValues();
  const sh_names = filter_sh.getRange('A1:A'+filter_sh.getLastRow()).getValues().flat();
  sh_names.forEach(s=>{
    if(!ss.getSheetByName(s)){
    ss.insertSheet().setName(s);}
    let sheet = ss.getSheetByName(s);   
    let f_data = data.filter(r=>r[0].includes(s));
    if(f_data.length>0){
    sheet.getRange(sheet.getLastRow()+1,1,f_data.length,f_data[0].length).setValues(f_data);}
}); 
}
    Login or Signup to reply.
  2. 0

    This function will place all of your results into column 4 next to the appropriate word rather than creating a page for each word. So it runs much faster.

    function stringswords() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const sr=2;
  const rgd=sh.getRange(sr,1,sh.getLastRow()-sr+1,2);
  const data=rgd.getDisplayValues();
  const rgw=sh.getRange(sr,3,sh.getLastRow()-sr+1,1);
  const words=rgw.getDisplayValues().flat();
  const wiObj={};
  words.forEach(function(w,i){wiObj[w]=i});
  const rgr=sh.getRange(sr,4,sh.getLastRow()-sr+1,1);
  rgr.clearContent();
  var results=rgr.getValues();
  words.forEach(function(w,i,A){
    data.forEach(function(r,j,D) {
      if(data[j][0] && data[j][0].indexOf(w)!=-1) {
        results[wiObj[w]][0]+=Utilities.formatString('String:%s Vol:%sn',data[j][0],data[j][1]);
      }
    });
  });
  rgr.setValues(results);
}

    Image of Data and output:

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search